MySQL join - Select table based on condition

Question

I have three tables table1, table2 and table2. table1.parent_id is the id of a record in table2 or table3, depending on the value of table1.parent_type. Now I want to join table1 with table2 and table3, depending on the value of table1.parent_type.

This can be done using UNION. But is there any other method?

This is my current query

 (SELECT c.*, a.title 
FROM table1 c 
LEFT OUTER JOIN table2 a 
ON c.parent_id = a.id 
WHERE c.parent_type = 0) 
UNION (SELECT c.*, p.title 
FROM table1 c 
LEFT OUTER JOIN table3 p 
ON c.parent_id = p.id 
WHERE c.parent_type = 1) 
ORDER BY id DESC 
LIMIT 10

Update : This is another method (From Dark Falcon's reply)

SELECT c.*, IF(c.parent_type = 0, a.title, p.title) as title FROM table1 c 
LEFT OUTER JOIN table2 a ON c.parent_id = a.id AND c.parent_type = 0  
LEFT OUTER JOIN table3 p ON c.parent_id = p.id AND c.parent_type = 1 
WHERE a.id IS NOT NULL OR p.id IS NOT NULL ORDER BY id DESC LIMIT 10;

Update 2 : I profiled the queries with query profiler. The multi table join is more than 100 times faster for all my test runs.

What is the likelihood that a parent_id will have records in both types? If that is the case, do you want to see both or one of those title values? — OMG Ponies
It will be either table2 or table2, not both. There will be one and only one record. — Joyce Babu

Dark Falcon Dark Falcon · Accepted Answer · 2010-12-04T02:23:41

This is a little difficult without the schema, but something along these lines should work. Note thaty you can put any condition in the ON clause of the join. Why do you wish to avoid the UNION?

SELECT c.*, a.title FROM
 table1 c
 LEFT OUTER JOIN table2 a
  ON c.parent_id = a.id AND c.parent_type = 0
 LEFT OUTER JOIN table3 p
  ON c.parent_id = p.id AND c.parent_type = 1
WHERE a.id IS NOT NULL OR p.id IS NOT NULL
ORDER BY id DESC LIMIT 10

MySQL join - Select table based on condition

1 Answers