CI MySQL query joins tables and where statement does not return the whole row
I have 3 tables that I want to join, however when I use the where clause on the third table and the third table does not have it, it does not return the row from the first and second tables, m, using a 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 above data, if I use ($ this-> db-> where ('c.trans_location', '1')), the result will not return account 4 as there is no acc_PID = '4' data in table_2 and table_3, however I want the result to return Account 4 even if there is no account 4 data in table 2 and table 3, without $ this-> db-> where ('c.trans_location', '1'), the result is also shows account 4, however, if the location operator does not return a row from table 1, although I used a left join, shouldn't it also return a result from table 1?
Thanks in advance.
source to share
Try adding a clause in the Join membership instead of this where. If you write the condition in the where clause, it will add the condition after filtering the join filter after the filter,
OR don't use Left join and add where the condition is finally.
One more thing: I didn't find any relation of table 1 to tanle 2 or table 3. If journal_account
there is a relation to table 1 then it should work.
I try, this is my 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 it 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 accounts Accoun1 and Account 2
Hope this helps you.
source to share