Adding missing rows in table in 1 select (+ join) command.

[huckfinn19]

Hi all! Here's what I need to do :

I have two tables :
A B
a b c d
---- ----
1 z 1 k
2 x 5 l
3 c 6 j

I need a SELECT with JOIN that would give me :
A
a b
----
1 z
2 x
3 c
5 NULL
6 NULL

so I need to add the missing rows from the A.a and B.c JOIN,
how can I do that ?

I can't use a Union 'cause I can't use MySQL version 4.

Thanks,

Simon

 

[abombss]

I do not know if there is an easier way or not but this is what I came up with. Hope it helps.

abombss

select
if( b2.c is null,a2.a,ifnull(a1.a,b1.c)) as "Key",
if( b2.c is null,a2.b,if( a1.a is null, b1.d, a1.b)) as "Val"
from
(b b1 left join a a1 on(a1.a = b1.c)),
(a a2 left join b b2 on(a2.a = b2.c))
where
not ( a1.a is null and b2.c is null)
order by
if( b2.c is null,a2.a,ifnull(a1.a,b1.c))
;

+------+------+
| Key | Val |
+------+------+
| 1 | z |
| 2 | x |
| 3 | c |
| 5 | l |
| 6 | j |
+------+------+
5 rows in set (0.00 sec)