Using Sql Joins to get full picture or records

1

Good Afternoon Team

Back once again looking for the help of the community.

AIM: I have two tables. One is the golden source of information, and the other is whats currently in the DB. What i would like to do is compare the golden source information and whats in the db and derive an outcome

Golden Source
Select
Account_Number,
id
from Golden
where account_number = '1234'

Returns the following results

Account Code Golden
1234 AAA
1234 BBB
1234 CCC
1234 DDD
1234 FFF

Current DB

select
Account_Code__C,
id
from Current

Returns the following

Account Code Current
1234 AAA
1234 BBB
1234 CCC
1234 DDD
1234 EEE

As you can see in the Golden source i have an additional id of FFF which is not in current . The expect Expected out come should look something like this ;

Account Code Current Golden Outcome
1234 AAA AAA Match
1234 BBB BBB Match
1234 CCC CCC Match
1234 DDD DDD Match
1234 EEE Delete
1234 FFF Adding

Looking forward to your help

2

First some consumable test data which you should have provided:

CREATE TABLE #Golden
(
	AcountCode varchar(20) NOT NULL
	,Golden varchar(20) NOT NULL
	,PRIMARY KEY (AcountCode, Golden)
);
INSERT INTO #Golden
VALUES ('1234', 'AAA')
	,('1234', 'BBB')
	,('1234', 'CCC')
	,('1234', 'DDD')
	,('1234', 'FFF');

CREATE TABLE #Current
(
	AcountCode varchar(20) NOT NULL
	,[Current] varchar(20) NOT NULL
	,PRIMARY KEY (AcountCode, [Current])
);
INSERT INTO #Current
VALUES ('1234', 'AAA')
	,('1234', 'BBB')
	,('1234', 'CCC')
	,('1234', 'DDD')
	,('1234', 'EEE');

Here is one approach:

SELECT COALESCE(G.AcountCode, C.AcountCode) AS AcountCode
	,C.[Current], G.Golden
	,CASE
		WHEN G.AcountCode IS NULL
		THEN 'Delete'
		ELSE 'Adding'
	END AS Outcome
FROM #Golden G
	FULL JOIN #Current C
		ON G.AcountCode = C.AcountCode
			AND G.Golden = C.[Current];
2 Likes