MySQL：如果列值为2，则X ELSE执行Y.

Question

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.

Answer 1

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.