当其中一个表中没有记录时，不显示记录

Question

I am using Join query to get data from multiple table which is working.

Now my issue is, when there is no records in one of the table(tbl_Fees or membership_details) then I am not getting the records. Some time I am getting error. It should be display atleast my members records.

I tried below code

Model

public function Memberinfo(){
   $getDetails = array('members.member_id'=>$this->session->userdata['login_session']['id'],'members.is_status'=>1);
   $result = $this->db->where($getDetails)
                      ->select('*')
                      ->from('members')
                      ->join('membership_details', 'members.member_id = membership_details.member_id','LEFT')
                      ->join('tbl_Fees', 'membership_details.Fees_id = tbl_Fees.Fees_id')
                      ->get()
                      ->row();
                return $result;

        } 

Would you help me out in this?

Answer 1

As a commenter pointed out, this is because of the join you have specified. Using JOIN will default to an inner join - hence why you are not getting your desired result.

Here are the different types of the JOINs available in SQL:

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table

RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table

FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

For reference, please see the images below which should help you visualize the joins more easily.

And finally, the join you are probably looking for:

This should be enough information for you to correct your code.