Maurice van Peursem wrote:
Hello,
I like to have a full outer join. if you have the following tables:
t1:
id | val
1 | A
2 | B
t2:
id | val
1 | B
2 | C
SELECT t1.id, t2.id, t1.val FROM t1 FULL OUTER JOIN t2 ON t1.val=t2.val
ORDER BY t1.id,t2.id
I want to get the following result (and in this order):
t1.id | t2.id | t1.val
1 | NULL | A
2 | 1 | B
NULL | 2 | C
As far as I can tell this is not possible in MySQL. Is there a way to
construct a MySQL statement that produces this result?
Maurice
Yes, you can produce this result using the union of two left joins:
(SELECT t1.id AS 't1_id', t2.id AS 't2_id', t1.val
FROM t1 LEFT JOIN t2 ON t1.val=t2.val)
UNION DISTINCT
(SELECT t1.id AS 't1.id', t2.id AS 't2.id', t2.val
FROM t2 LEFT JOIN t1 ON t1.val=t2.val)
ORDER BY t1_id IS NULL, t1_id, t2_id IS NULL, t2_id;
Note I added checks for NULL in the ORDER BY clause, because NULLs ordinarily
come first, but you wanted them last.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
