SQL: Full Outer Join across two hierarchies

Question

Let's say I have a table of parts that were on a shipment and parts that were on a receipt. I want to match up what I shipped compared to what I received, bound together by a common Shipment Number.

Shipment Table

SHIPMENT NO    PART NO
 1              A
 1              B
 2              A
 2              C

Receipt Table

SHIPMENT NO    PART NO
 1              A
 1              C
 2              B
 3              A

Desired Results

SHIP. SHIP. NO    SHIP. PART NO    RPT. SHIP. NO    RPT. PART NO
 1                 A                1                A
 1                 B                1                NULL
 1                 NULL             1                C
 2                 A                2                NULL
 2                 NULL             2                C
 NULL              NULL             3                A

So the idea would be that there is a full outer join showing all distinct shipment numbers and respective part numbers for both shipments and receipts, but joining them together where they match. The complication is still joining on the Shipment No even if the part numbers don't match.

There are basically two join conditions, one of which is completely optional.

I'm certain the solution is very simple but I can't see how to do this without using unions.

so, just to confirm, is the ¨Part no¨ join condition optional? — gerosalesc, Oct 01 '15 at 04:19

score 4 · Accepted Answer · answered Oct 01 '15 at 05:06

Try this

CREATE TABLE Shipment ([SHIPMENT NO] int, [PART NO] varchar(1))    
INSERT INTO Shipment ([SHIPMENT NO], [PART NO])
VALUES
    (1, 'A'),
    (1, 'B'),
    (2, 'A'),
    (2, 'C')
;
CREATE TABLE Receipt ([SHIPMENT NO] int, [PART NO] varchar(1));    
INSERT INTO Receipt  ([SHIPMENT NO], [PART NO])
VALUES
    (1, 'A'),
    (1, 'C'),
    (2, 'B'),
    (3, 'A')
;

SELECT 
     X.ShipmentShipNO AS 'SHIP. SHIP. NO'   
    ,s.[PART NO] AS 'SHIP. PART NO'
    ,X.ReceiptShipNO AS 'RPT. SHIP. NO'
    ,r.[PART NO] AS 'RPT. PART NO'

FROM Shipment s
FULL OUTER JOIN Receipt r ON s.[SHIPMENT NO] = r.[SHIPMENT NO] AND s.[PART NO] = r.[PART NO]
FULL OUTER JOIN 
                (
                    SELECT DISTINCT
                        s.[SHIPMENT NO] AS'ShipmentShipNO'
                        ,r.[SHIPMENT NO] AS'ReceiptShipNO'
                        FROM Shipment s
                        FULL JOIN Receipt r ON s.[SHIPMENT NO] = r.[SHIPMENT NO]
                )X ON r.[SHIPMENT NO] = X.ReceiptShipNO OR s.[SHIPMENT NO] = X.ShipmentShipNO
ORDER BY 3


DROP TABLE Shipment
DROP TABLE Receipt

Welcome to stackoverflow. I think you got it right. Have you check [**sqlFiddle**](http://sqlfiddle.com/#!6/9a080/2) That help when you want show a working answer. Have a nice `text to ddl` function to help prepare the data schema. And a plaint text output so you can paste the output as answer instead of a picture — Juan Carlos Oropeza, Oct 01 '15 at 15:58
Great! I knew there was a basic join style that would accomplish this but haven't previously had a reason to use `full join`. ...which is embarrassing, because I even used the phrase `full outer join` in my post... — Devin Burke, Oct 05 '15 at 14:27
@Justin, they(Full Join, Full Outer Join) are both same. So like Left Join/Left outer join and same holds for right/right outer join — RNA Team, Oct 30 '15 at 13:39

score 1 · Answer 2 · answered Oct 01 '15 at 02:53

1

Use FULL JOIN

SQL Fiddle Demo

SELECT S.*, R.*
FROM Shipment S 
FULL JOIN Receipt R
 ON S.[SHIPMENT NO] = R.[SHIPMENT NO]
AND S.[PART NO] = R.[PART NO]

answered Oct 01 '15 at 02:53

Juan Carlos Oropeza

47,252
12
78
118

SQL: Full Outer Join across two hierarchies

2 Answers2