Codeigniter Active Record / MySQL Join Query - How to Return Results if one of the Table Rows is Not Present

Question

I have the following query:

$this->db
     ->select('SQL_CALC_FOUND_ROWS null as rows
        ,table1.*
        ,table2.*
        ,table3.*', FALSE)
     ->from('table1')
     ->where('table1.column1', $user_id)
     ->join('table2', 'table2.column2 = table1.column2')
     ->join('table3', 'table3.column2 = table1.column2')
     ->group_by('table1.column2')
     ->order_by('table1.column2', 'DESC');

 $query = $this->db->get();

The problem is, there may not be a row in table 3 and if there is not, I would still like to return a result with the remainder of the query data. Please could someone advise how to achieve this?

Raman Buzaubakov · Accepted Answer · 2013-07-12 23:13:43Z

1

you should do a left join on table3

answered Jul 12, 2013 at 23:13

Raman Buzaubakov

5034 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

whispersan Over a year ago

thanks, I had tried that and hadn't found it successful. 'Table 2' returns more than one row, so the query then doesn't then return all the rows of table 2

MJ X · Accepted Answer · 2013-07-13 05:06:26Z

0

Use left join and also use group_by to get exact records:

$this->db->join('login_ranknames AS t4', 't4.id = t1.rank', 'left');
$this->db->group_by('t4.id');

answered Jul 13, 2013 at 5:06

MJ X

9,12413 gold badges85 silver badges124 bronze badges

Collectives™ on Stack Overflow

Codeigniter Active Record / MySQL Join Query - How to Return Results if one of the Table Rows is Not Present

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related