CODEWITHSUNDEEP
sql
coson
coson

Reputation: 8659

Full Outer Join Duplicate Results

Good Day,

This may seem long (but it's the data I'm showing) I have two tables. TestA and TestB. TestA has two columns (INT, VARCHAR(32)) called RowNumber and InvoiceNumber. The following result is: SELECT RowNumber, InvoiceNumber FROM TestA.

1   GB01530111600236
2   GB01530111600236
3   GB01530111600236
1   GB01530121600001
2   GB01530121600001

TestB is identical in that there are two columns with the same name.

1   GB01530111600236
1   GB01530121600001
2   GB01530121600001
3   GB01530121600001
4   GB01530121600001
5   GB01530121600001
6   GB01530121600001
7   GB01530121600001
8   GB01530121600001
9   GB01530121600001
10  GB01530121600001
11  GB01530121600001
12  GB01530121600001

My understanding of a FULL OUTER JOIN is that it will match all rows from both tables based on a JOIN and WHERE clause. The reason is that TableA can have 1 or more invoice numbers and that TableB can also have 1 or more invoice numbers.

So from TestA, there are three invoice numbers of 'GB01530111600236' and one in TestB with 'GB01530111600236'. In addition, there are 12 invoice numbers in TestB that are 'GB01530121600001' and two in TestA with 'GB01530121600001' So what I'm trying to get is:

[   Table A        ]    [   Table B        ]
1   GB01530111600236    1   G01530111600236
2   GB01530111600236    1   G01530111600236
3   GB01530111600236    1   G01530111600236
1   GB01530121600001    1   GB01530121600001
2   GB01530121600001    2   GB01530121600001
1   GB01530121600001    3   GB01530121600001
1   GB01530121600001    4   GB01530121600001
1   GB01530121600001    5   GB01530121600001
1   GB01530121600001    6   GB01530121600001
1   GB01530121600001    7   GB01530121600001
1   GB01530121600001    8   GB01530121600001
1   GB01530121600001    9   GB01530121600001
1   GB01530121600001    10  GB01530121600001
1   GB01530121600001    11  GB01530121600001
1   GB01530121600001    12  GB01530121600001

My query is: 

SELECT A.RowNumber, B.RowNumber, A.InvoiceNumber, B.InvoiceNumber
FROM TestA A 
    FULL OUTER JOIN TestB B ON A.InvoiceNumber = B.InvoiceNumber

This query is half way right in that it gives me the correct output for 'GB01530111600236'. The abridged version displays only 1-5 instead of 1 to 12.

1   GB01530111600236    1   GB01530111600236
2   GB01530111600236    1   GB01530111600236
3   GB01530111600236    1   GB01530111600236
1   GB01530121600001    1   GB01530121600001
1   GB01530121600001    2   GB01530121600001
1   GB01530121600001    3   GB01530121600001
1   GB01530121600001    4   GB01530121600001
1   GB01530121600001    5   GB01530121600001
2   GB01530121600001    1   GB01530121600001
2   GB01530121600001    2   GB01530121600001
2   GB01530121600001    3   GB01530121600001
2   GB01530121600001    4   GB01530121600001
2   GB01530121600001    5   GB01530121600001

But for the second invoice number 'GB01530121600001', it's giving me duplicates. How do I get rid of the duplicates?

TIA,

coson

Upvotes: 2

Views: 3260

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

I don't follow the logic for your result set. The following, however, should give the same results:

SELECT COALESCE(A.RowNumber, 1),
       COALESCE(B.RowNumber, 1),
       COALESCE(A.InvoiceNumber, B.InvoiceNumber),
       COALESCE(A.InvoiceNumber, B.InvoiceNumber)
FROM TestA A FULL OUTER JOIN
     TestB B
     ON A.InvoiceNumber = B.InvoiceNumber AND
        A.RowNumber = B.RowNumber;

Upvotes: 1

Related Questions