Reputation: 85
every one.
I have 2 tables.
tbl_a
id Col_A Col_B
1 1 2
3 3 4
tbl_b
id Col_C Col_D
1 100 101
2 102 103
4 104 105
I want to make a table such as:
id Col_A Col_B Col_C Col_D
1 1 2 100 101
2 0 0 102 103
3 3 4 0 0
4 0 0 104 105
I tried the SQL from How to do a FULL OUTER JOIN in MySQL?, but it wasn't the right result I want.
SQL:
SELECT * FROM tbl_a
LEFT JOIN tbl_b ON tbl_a.id = tbl_b.id
UNION
SELECT * FROM tbl_a
RIGHT JOIN tbl_b ON tbl_a.id = tbl_b.id
Result:
id Col A Col B id(1) Col C Col D
1 1 2 1 100 101
3 3 4 (NULL) (NULL) (NULL)
(NULL) (NULL) (NULL) 2 102 103
(NULL) (NULL) (NULL) 4 104 105
How can I get the correct result? Any help will be appreciated.
Thank you.
Upvotes: 1
Views: 55
Reputation: 230
Try like this
SELECT
IFNULL(tbl_a.id, tbl_b.id) AS id,
IFNULL(tbl_a.Col_A, 0) as Col_A,
IFNULL(tbl_a.Col_B, 0) as Col_B,
IFNULL(tbl_b.Col_C, 0) as Col_C,
IFNULL(tbl_b.Col_D, 0) as Col_D
FROM
tbl_a
LEFT JOIN
tbl_b ON tbl_a.id = tbl_b.id
UNION
SELECT
IFNULL(tbl_a.id, tbl_b.id) AS id,
IFNULL(tbl_a.Col_A, 0) as Col_A,
IFNULL(tbl_a.Col_B, 0) as Col_B,
IFNULL(tbl_b.Col_C, 0) as Col_C,
IFNULL(tbl_b.Col_D, 0) as Col_D
FROM
tbl_a
RIGHT JOIN
tbl_b ON tbl_a.id = tbl_b.id
Upvotes: 0
Reputation: 2281
This will give the desired result:
SELECT * FROM
(
SELECT tbl_a.id,
tbl_a.Col_A,
tbl_a.Col_B,
COALESCE(tbl_b.Col_C, 0) AS Col_C,
COALESCE(tbl_b.Col_D, 0) AS Col_D
FROM tbl_a
LEFT JOIN tbl_b ON tbl_a.id = tbl_b.id
UNION
SELECT tbl_b.id,
COALESCE(tbl_a.Col_A, 0) AS Col_A,
COALESCE(tbl_a.Col_B, 0) AS Col_B,
tbl_b.Col_C,
tbl_b.Col_D
FROM tbl_a
RIGHT JOIN tbl_b ON tbl_a.id = tbl_b.id
)
AS a
ORDER BY id;
Upvotes: 1