Full Outer Join based off multiple fields
Solution 1
Tyr this to get the results that you have posted:
SELECT COALESCE(a.Operation_Key, b.Operation_Key) Operation_Key,
COALESCE(a.Part_Key, b.Part_Key) Part_Key,
Record_Key,
CASE
WHEN Record_Key IS NULL THEN 'Add'
ELSE 'Delete'
END Action
FROM TableA a FULL OUTER JOIN TableB b
ON a.Operation_Key = b.Operation_Key
AND a.Part_Key = b.Part_Key
WHERE (a.Operation_Key IS NULL) OR (b.Operation_Key IS NULL)
Test Script:
CREATE TABLE #TableA
(
Operation_Key INT,
Part_Key INT
)
INSERT INTO #TableA
SELECT 1,1
UNION
SELECT 1,2
UNION
SELECT 2,1
UNION
SELECT 2,3
CREATE TABLE #TableB
(
Operation_Key INT,
Part_Key INT,
Record_Key INT
)
INSERT INTO #TableB
SELECT 1,1,1
UNION
SELECT 2,1,2
UNION
SELECT 2,3,3
UNION
SELECT 2,4,4
SELECT COALESCE(a.Operation_Key, b.Operation_Key) Operation_Key,
COALESCE(a.Part_Key, b.Part_Key) Part_Key,
Record_Key,
CASE
WHEN Record_Key IS NULL THEN 'Add'
ELSE 'Delete'
END Action
FROM #TableA a FULL OUTER JOIN #TableB b
ON a.Operation_Key = b.Operation_Key
AND a.Part_Key = b.Part_Key
WHERE (a.Operation_Key IS NULL) OR (b.Operation_Key IS NULL)
Output:
Operation_Key Part_Key Record_Key Action
1 2 NULL Add
2 4 4 Delete
Solution 2
Add to B:
insert into B (Operation_Key, Part_Key, Record_Key)
values
select Operation_Key, Part_Key, null as Record_Key from A
left join b on a.Operation_Key = b.Operation_Key and
a.Part_Key = b.Part_Key
where b.Part_Key is null
Delete from B:
Delete from B
select * from B left join A on b.Operation_Key = a.Operation_Key and
b.Part_Key = a.Part_Key
where a.Operation_Key is null
Solution 3
You can get exactly the results table you want (looking at your example set up) by making clever use of the SQL "COALESCE" operator. If you use a query like this:
SELECT
COALESCE(A.Operation_Key, B.Operation_Key) as Operation_Key,
COALESCE(A.part_key, B.part_key) as Part_Key,
B.Record_Key,
CASE WHEN A.Operation_Key IS NULL THEN
'Delete'
ELSE
'Add'
END AS [Action] FROM A
FULL OUTER JOIN B
ON A.Operation_Key = B.Operation_Key
AND A.Part_Key= B.Part_Key
WHERE A.Operation_Key IS NULL
OR B.Operation_Key IS NULL
...you'll get a result table exactly like your example.
Comments
-
NA Slacker about 4 years
Here is the situation that I'm facing:
I have two tables A and B. If records are in table A and not in table B they need to be added to table B. If records are in table B and not in table A, then they need to be removed out of table B. The trick here is that it is the mixture of two keys that makes the unique combination
Table A Operation_Key Part_Key 1 1 1 2 2 1 2 3 Table B Operation_Key Part_Key Record_Key 1 1 1 2 1 2 2 3 3 2 4 4
I am trying to get the right type of query so that the results returned look like
Results Operation_Key Part_Key Record_Key Action 1 2 NULL Add 2 4 4 Delete
The query I have so far looks like similar to this:
CREATE TABLE #Action_Table ( Action VARCHAR(6), Action_Bit INT, Operation_Key INT, Record_Key INT, Part_Key INT ) INSERT INTO #Action_Table SELECT CASE WHEN WS.Operation_Key IS NULL THEN 'Delete' WHEN WS.Operation_Key IS NOT NULL THEN 'Add' END Action, CASE WHEN WS.Operation_Key IS NULL THEN '0' WHEN WS.Operation_Key IS NOT NULL THEN '1' END Action_Bit, CASE WHEN WS.Operation_Key IS NULL THEN WC.Operation_Key WHEN WS.Operation_Key IS NOT NULL THEN WS.Operation_Key END Operation_Key, CASE WHEN WS.Operation_Key IS NULL THEN WC.Record_Key WHEN WS.Operation_Key IS NOT NULL THEN NULL END Workcenter_Component_Key, CASE WHEN WS.Operation_Key IS NULL THEN WC.Part_Key WHEN WS.Operation_Key IS NOT NULL THEN WS.Part_Key END Part_Key FROM #WS_Part_Table WS FULL OUTER JOIN #WC_Part_Table WC ON WC.Part_Key = WS.Part_Key AND WC.Operation_Key = WS.Operation_Key WHERE (WS.Part_Key IS NULL or WC.Part_Key IS NULL) AND (WS.Operation_Key IS NULL or WC.Operation_Key IS NULL)
Both the #WS_Part_Table and the #WC_Part_Table are temp tables that I'm constructing using queries, but my dilemma is that I have to PRE-QUERY the #WC_Part_Table query on the operation key that I'm interested in, otherwise I get way too many results.
This is the query that I'm using to create the #WC_Part_Table
CREATE TABLE #WC_Part_Table ( Operation_Key INT, Record_Key INT, Part_Key INT ) -- Workcenter Component Table INSERT INTO #WC_Part_Table SELECT O.Operation_Key, WC.Record_Key, WC.Part_Key FROM Workcenter_Component WC JOIN Operation O ON O.Default_Workcenter_Key = WC.Workcenter_Key /* There is some reason why this next line is needed */ WHERE O.Operation_Key = 23149