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
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)