Outer Join on multiple tables

Question

select table3.tid, table3.name, talble4.name1, table4.name2 from table3 left join 
(select table1.tid, table1.name as name1, table2.name as name2 from table1 left join table2
 on table1.tid = table2.tid
 union
 select table2.tid, table1.name, table2.name from table1 right join table2
 on table1.tid = table2.tid) as table4;

Please tell me what is wrong here.

I want a full outer join of the 3 tables : table1, table2 and table3 (which MYSQL does not support)

why not just do it like this http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — Jonesopolis, Jul 08 '13 at 17:41
Please provide the real table names. table1, table2, table3 are really hard to read. — ypercubeᵀᴹ, Jul 08 '13 at 17:41
@ypercube: the entire query is hard to read. it's not just the table names; it's the unfortunate formatting as well. The introduction of "table4" as an alias for an inline view doesn't improve anything either. — spencer7593, Jul 08 '13 at 18:00

spencer7593 · Answer 1 · 2013-07-08T18:08:12.840

I would emulate a "full outer join" of three tables with three separate queries, with the rows concatenated together with UNION ALL operations.

The first query is all the tid values from table1. The second query gets all the tid values from table2 which don't exist in table1. The third query gets all the tid values in table3 which don't exist in table1 and don't exist in table2. The "trick" in the second and third queries is including appropriate

tid IS NULL

predicates in the WHERE clause, to make sure the tid values returned from the prior queries are omitted. (If we aren't guaranteed that tid is NOT NULL, we would probably want to avoid returning NULL values for tid by including an appropriate tid IS NOT NULL predicate in each of the queries for the "driving" table, which in the example below is the table following the FROM keyword.)

The last step is to include the name columns in the select list. For consistency, I'd put the name value from table1 in the same column. (In the second query, the name1 column will always be NULL, in the third query, the name1 and name2 columns will always be NULL.)

SELECT a.tid
     , a.name AS name1
     , b.name AS name2
     , c.name AS name3
  FROM table1 a
  LEFT
  JOIN table2 b ON b.tid = a.tid
  LEFT
  JOIN table3 c ON c.tid = a.tid
 UNION ALL
SELECT d.tid
     , f.name AS name1
     , d.name AS name2
     , e.name AS name3
  FROM table2 d
  LEFT
  JOIN table3 e ON e.tid = d.tid
  LEFT
  JOIN table1 f ON f.tid = d.tid
 WHERE f.tid IS NULL
 UNION ALL
SELECT g.tid
     , h.name AS name1
     , i.name AS name2
     , g.name AS name3
  FROM table3 g
  LEFT
  JOIN table1 h ON h.tid = g.tid
  LEFT
  JOIN table2 i ON i.tid = g.tid
 WHERE h.tid IS NULL
   AND i.tid IS NULL

score 0 · Answer 2 · answered Jul 08 '13 at 17:43

Hi you don't seem to have an "on table3.JoinColumn = table4.JoinColumn" after table4 in your SQL. I think you've been marked down as you didn't say what your error is, and I think your question is a little vague. But perhaps the SQL I've given may be all your need to complete your task...

Outer Join on multiple tables

2 Answers2