MySQL join - condition based table selection

Question

I have three tables table1, table2 and table2. table1.parent_id is the identifier of the entry 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 request.

(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 

Refresh . This is another method (from Dark Falcon answer).

 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 have profiled queries using the query profiler. Joining with multiple tables is over 100 times faster for all of my test runs.

Answer 1

This is a little complicated without a scheme, but something in this direction should work. Note that you can put any condition in an ON clause of a join. Why do you want 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