Posted by undercups on 06/12/07 14:24

The answer is to write 3 queries, 1 for each situation you describe
and link the output using the UNION ALL statement

Something like

SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a JOIN table2
b on a.id = b.id
WHERE a.col1 <> b.col1
UNION ALL
SELECT a.id, a.col1, a.col2, a.col3, b.col4 FROM table1 a LEFT JOIN
table2 b on a.id = b.id
WHERE b.col4 IS NULL
UNION ALL
SELECT b.id, b.col1, b.col2, b.col3, a.col4 FROM table2 b LEFT JOIN
table1 a on b.id = a.id
WHERE a.col4 IS NULL

Note that the col1, col2, col3 and col4 in each of the queries must be
of the same datatypes or can be converted to the same datatypes e.g.
VARCHAR(20) is the same as CHAR(5). The where clauses can be more
specific and varied than I have shown but the important part is the
test for IS NULL in the last 2 queries as this is the way to find rows
in one table and that are not in another.

Hope this helps

[Back to original message]