# Unexpected Row of Data when Joining two tables

I want to join both tables, and its giving me a much harder time and it should be, at least it doesn't look that complicated. Would be easier if table b would have a value for ID: 0, but it doesn't. So how can I get rid of the double rows in Table D

Table a

``````Article | ID  | Misc |
----------------------
x1      |0    |a     |
x2      |2    |b     |
x3      |1    |c     |
``````

Table b

``````Article | ID  | Misc2|
----------------------
x2      | 1   | b-1      |
x2      | 2   | b-2      |
x3      | 1   | c-1      |
x3      | 2   | c-2      |
``````

What I want is Table C

``````Article | ID  | Misc | Misc2 |
------------------------------
x1      | 0   | a    |       |
x2      | 1   | b    | b-1   |
x2      | 2   | b    | b-2   |
x3      | 1   | c    | c-1   |
x3      | 2   | c    | c-2   |
``````

What I get is Table D

``````Article | ID  | Misc | Misc2 |
------------------------------
x1      | 0   | a    |       | correct
x2      | 0   | b    | b-1   | ID false
x2      | 1   | b    | b-1   | correct
x2      | 0   | b    | b-2   | ID false
x2      | 2   | b    | b-2   | correct
x3      | 0   | c    | c-1   | ID false
x3      | 1   | c    | c-2   | correct
x3      | 0   | c    | c-1   | ID false
x3      | 2   | c    | c-2   | correct
``````

My query:

``````Select
a.Article,
a.ID,
a.Misc,
b.Misc2
From
[table] a
Inner Join
[table] b
On
b.article =  a.article
And (
a.ID = 0
OR  a.ID = b.ID
``````

## 1 Answer

You need to use `LEFT` (outer) join. Try this:

``````SELECT
a.Article,
COALESCE(b.ID, 0) AS ID,
a.Misc,
b.Misc2
FROM
[table] a
LEFT JOIN
[table] b
ON
b.article =  a.article ;
``````

You may want to use `COALESCE(b.ID, a.ID)` instead of `COALESCE(b.ID, 0)`, depending on what you want to show (`a.ID` or always `0`) for articles that appear in `a` but not `b`.

Tested in: dbfiddle.uk
(test kindly provided by @McNets)