The database I am working on right now is somewhat messy. I have three potential tables, that I want to join but in some cases it may only be two tables. Let's call these table1, table2 and table3.
table1 has a field called "type". If table1.type is 2, then I only need to join table3. For any other values I want to join table2 and then table3.
How can I achieve this in one single SQL query rather than: 1) having one query to select the type. 2) make a PHP foreach-loop to check the type of the current iteration and 3) perform a new query according to the type value.
Edit: I'll try to be more specific.
table1 has a column named "pid" that references to a whole other table, but that's redundant to this question. I tried working my ways around with UNIONs and LEFT JOINs but couldn't manage to achieve what I was looking for.
I want to select all results from my database with the "pid" value being "100". This gives me four rows in return, where was 2 of them are of type value "2" and the others are "1".
So basically what I want to achieve is the following two SQL statements in one:
(If "type" is "2")
SELECT *
FROM table1 t1
INNER JOIN table3 t3
ON t1.id = t3.t1_id
WHERE t1.pid = 100
(If "type" is NOT "2")
SELECT *
FROM table1 t1
INNER JOIN table2 t2
ON t1.id = t2.t1_id
INNER JOIN table3 t3
ON t2.id = t3.t2_id
WHERE t1.pid = 100
I'm guessing I could manage to do this with a UNION statement, but I'm confused on how to implement the WHERE t1.pid = '100' part.
use an UNION e.g.
SELECT t1.*, t3.*
FROM table1 t1
INNER JOIN table3 t3 ON t1.id = t3.t1_id
WHERE t1.pid = 100 and t1.type = 2
UNION
SELECT t1.*, t3.*
FROM table1 t1
INNER JOIN table2 t2
ON t1.id = t2.t1_id
INNER JOIN table3 t3 ON t2.id = t3.t2_id
WHERE t1.pid = 100 and t1.type <> 2;
but it would be better to explicitly name the columns you want to get back.