I have 3 tables:
Table 1 Table 2 Table 3 id_1 [...] id_1, type, id_2 id_2 [...]
I want to join table 1 to table 3 by way of table 2. This is easy enough,eg.
$data['table3']['table']['join']['table1'] = array( 'left_table' => 'table2', 'left_field' => 'id_2', 'field' => 'id_2', ); $data['table2']['table']['join']['table1'] = array( 'left_field' => 'id_1', 'field' => 'id_1', );
However, the intermediary table also has a 'type' attribute in it. What if I want to specify, as part of the join, what the type is? eg. I want something like this:
SELECT id_a.table1 FROM table3 LEFT JOIN table2 ON table3.id_2 = table2.id_2 AND table2.type = 'blah' LEFT JOIN table1 ON table2.id_1 = table2.id_1;
To be clear, I want the condition to the part of the join, not after the fact (as a filter will remove null rows).