使用join语句，但如果table为空

Question

When create a query, I want to use join twice like the following:

$this->db->select('*');
$this->db->from('members');
$this->db->join('members_group', 'members_group.mgid = members.mgid');
$this->db->join('members_profiles','members_profiles.mid = members.mid');
$this->db->where('members.mid=' . $id);
$this->db->get()

The problem in the join statement number 2, If there is no data within the members_profiles table, the query return empty array. but if I remove the join statement number 2, the result will be correct data.

How can I make the query return data whether members_profiles table contains of data that related to a member or not?

Answer 1

Codeigniter allows variant join types to be specified in the third parameter of the join() method.

Try this:

 $this->db->join('members_profiles','members_profiles.mid = members.mid', 'left');

An ordinary (inner) join suppresses records from the first (left-hand) table that don't match the second (right-hand) table. A left join preserves records on the left that don't match records on the right. It places NULL values in the columns of the resultset that should have come from the missing right-hand table. This does exactly what you want.