Reputation: 14296
Given two tables APPLE and ORANGE,
NAME APPLES
Alice 5
Bob 10
Trudy 1
NAME ORANGES
Bob 50
Trudy 10
Dick 10
How can I write a JOIN to show the table:
NAME APPLES ORANGES
Alice 5 -
Bob 10 50
Trudy 1 10
Dick - 10
I currently have
SELECT a.NAME, APPLES, ORANGES
FROM APPLE a
JOIN
ORANGE o ON o.NAME = a.NAME
but that only returns the fields that have a value in both APPLE and ORANGE.
Upvotes: 4
Views: 1337
Reputation: 13665
should be:
SELECT COALESCE(a.NAME,o.name) as Name, APPLES, ORANGES
FROM APPLE a
FULL OUTER JOIN ORANGE o ON o.NAME = a.NAME
Example: http://sqlfiddle.com/#!4/1ae9a/4
Upvotes: 3
Reputation: 19
you have to use a left right outer join depending on which table contains the inclomplete data
Upvotes: 0
Reputation: 13534
SELECT a.NAME, a.APPLES, o.ORANGES
FROM APPLE a
FULL OUTER JOIN
ORANGE o ON o.NAME = a.NAME
Upvotes: 4
Reputation: 171491
SELECT COALESCE(a.NAME, b.NAME) as NAME, APPLES, ORANGES
FROM APPLE a
FULL OUTER JOIN ORANGE o ON o.NAME = a.NAME
Upvotes: 7