I have this query to simulate full join
of three tables in mysql,
SELECT a. * , b. * , c. *
FROM tbl_1 a
LEFT OUTER JOIN tbl_2 b ON a.num = b.num
LEFT OUTER JOIN tbl_3 c ON a.num = c.num
UNION
SELECT a. * , b. * , c. *
FROM tbl_1 a
RIGHT OUTER JOIN tbl_2 b ON a.num = b.num
RIGHT OUTER JOIN tbl_3 c ON a.num = c.num
But the result it generates is not what i am expecting, the tables that i am going to join are as follows,
Table1-
num |info
----------
1 |aaaaa
2 |bbbb
3 |ccc
3 |ccc
4 |dddd
Table2-
num |info
----------
1 |aaaaa
3 |ccc
4 |dddd
5 |eeee
6 |ffff
Table3-
num |info
----------
1 |aaaaa
6 |ffff
2 |bbbb
And the result I am expecting is,
Required result 1-
num |info num |info num |info
----------- ----------- ----------
1 |aaaaa 1 |aaaaa 1 |aaaaa
2 |bbbb NULL|NULL 2 |bbbb
3 |ccc 3 |ccc NULL|NULL
3 |ccc 3 |ccc NULL|NULL
4 |dddd 4 |dddd NULL|NULL
NULL|NULL 5 |eeee NULL|NULL
NULL|NULL 6 |ffff 6 |ffff
OR
Required result 2-
num |info num |info num |info
----------- ----------- ----------
1 |aaaaa 1 |aaaaa 1 |aaaaa
2 |bbbb NULL|NULL 2 |bbbb
3 |ccc 3 |ccc NULL|NULL
3 |ccc 3 |ccc NULL|NULL
4 |dddd 4 |dddd NULL|NULL
NULL|NULL 6 |ffff 6 |ffff
But what i get is
Result i get-
num |info num |info num |info
----------- ----------- ----------
1 |aaaaa 1 |aaaaa 1 |aaaaa
2 |bbbb NULL|NULL 2 |bbbb
3 |ccc 3 |ccc NULL|NULL
3 |ccc 3 |ccc NULL|NULL
4 |dddd 4 |dddd NULL|NULL
NULL|NULL NULL|NULL 6 |ffff
NULL|NULL NULL|NULL 2 |bbbb
May i know what is wrong over here, i've been stuck with this for 2 days and couldn't resolve..may i know the query to obtain the result i want?
Thank you very much :)