CODEWITHSUNDEEP
mysqljoin
blarg
blarg

Reputation: 3893

How to join tables and include entries where not in both tables?

I have two tables like:

Table A

+-----------+---------------+
| AccountID | NumberOfAdams |
+-----------+---------------+
| A1        |             2 |
| A2        |             5 |
| A3        |             10|
+-----------+---------------+

Table B:

+-----------+----------------+
| AccountID | NumberOfBrowns |
+-----------+----------------+
| A3        |             13 |
+-----------+----------------+

Using a LEFT JOIN, with table B as the first table, the resulting table is:

+-----------+---------------+----------------+
| AccountID | NumberOfAdams | NumberOfBrowns |
+-----------+---------------+----------------+
| A3        |            10 |             13 |
+-----------+---------------+----------------+

What I want to get is:

+-----------+---------------+----------------+
| AccountID | NumberOfAdams | NumberOfBrowns |
+-----------+---------------+----------------+
| A1        |             2 | NULL           |
| A2        |             5 | NULL           |
| A3        |            10 | 13             |
+-----------+---------------+----------------+

How can I retrieve all AccountIDs from both with a JOIN?

Upvotes: 1

Views: 33

Answers (1)

ProDec
ProDec

Reputation: 5410

This query might work

SELECT p.AccountID, a.NumberOfAdams, b.NumberOfBrowns
FROM (
 SELECT AccountID
 FROM table_a
 UNION 
 SELECT AccountID
 FROM table_b
) p
LEFT JOIN table_a a ON p.AccountID = a.AccountID
LEFT JOIN table_b b ON p.AccountID = b.AccountID

Upvotes: 1

Related Questions