I have 3 tables that I want to join, however when I use where statement on the third table, and the third table doesn't have it, it does not return the row from the first and second table even though I'm using left join.
Table 1
+---------+--------------+----------+
| acc_PID | acc_name | acc_type |
+---------+--------------+----------+
| 1 | Account 1 | 1 |
| 2 | Account 2 | 1 |
| 3 | Account 3 | 2 |
| 4 | Account 4 | 1 |
+---------+--------------+----------+
Table 2
+-------------+-----------------+-----------+
| journal_PID | journal_account | trans_PID |
+-------------+-----------------+-----------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 1 | 3 |
+-------------+-----------------+-----------+
Table 3
+-----------+----------------+
| trans_PID | trans_location |
+-----------+----------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
+-----------+----------------+
// CI query
$this->db->join('table_2 b', 'a.acc_PID = b.journal_account', 'LEFT');
$this->db->join('table_3 c', 'b.trans_PID = c.trans_PID', 'LEFT');
$this->db->where('a.acc_type', '1');
$this->db->where('c.trans_location', '1');
$this->db->group_by('a.acc_PID');
$query = $this->db->get('table_1 a');
$result = $query->result();
Now from the data above, if I use ($this->db->where('c.trans_location', '1')), the result won't return Account 4 as there is no data of acc_PID ='4' in table_2 and table_3, however I want the result to return Account 4 too even though there is no data of account 4 in table 2 and table 3, without $this->db->where('c.trans_location', '1'), the result shows account 4 too, however with the where location statement it does not return the row from table 1 even though I used left join, shouldn't it return result from table 1 too ?
Thank you in advance.
Try to add condition in Join instead on where clause. If you write condition in where clause , it will add condition after the join means filter after filter,
OR do not use Left join and add where condition at last.
One more thing i did not found any relation of table 1 with tanle 2 or table 3.If journal_account is relation with table 1 then it should work.
I try it my self this is the solution i think :
SELECT * FROM `table1`
INNER JOIN table2 ON table2.journal_account = table1.acc_PID
INNER JOIN table3 ON table3.trans_PID = table2.trans_PID
WHERE table1.acc_type = 1 AND table3.trans_location = 1 GROUP BY table1.acc_PID
AND This Also :
SELECT * FROM `table1`
INNER JOIN table2 ON table2.journal_account = table1.acc_PID
INNER JOIN table3 ON table3.trans_PID = table2.trans_PID AND table3.trans_location = 1
WHERE table1.acc_type = 1 GROUP BY table1.acc_PID
This will give me two Accoun Account1 and Account 2
Hope this will help you.