Compare Data SQL Query help ....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Compare Data SQL Query help ....

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2012-12-05 : 14:50:25

Folks:

We have a master table (#tblGrpMaster) where we store the group access for each folder. At the end of each month we get data from other department regarding the actual access they have in each folder and the folder names are different. We then have to match the access against master table (#tblGrpMaster) and flag the differences. The data in master table is accurate. I would like help on the SQL statement. Here is the DDL and sample data. In the below example I have purposely added 2 records at the end which are not matching.

CREATE TABLE #tblGrpMaster
(
 FldrName VARCHAR(50),
 SecGroup VARCHAR(100),
 Access VARCHAR(20))
 
INSERT INTO #tblGrpMaster values ('Customer','Admin','Modify')
INSERT INTO #tblGrpMaster values ('Customer','DatabaseAdmin','Modify')
INSERT INTO #tblGrpMaster values ('Customer','Accounts','Read')
INSERT INTO #tblGrpMaster values ('Customer','Clients','Read')
INSERT INTO #tblGrpMaster values ('Customer','Documents','Modify')


CREATE TABLE #tblAccess
(
 ClientFldr VARCHAR(50),
 FldrName VARCHAR(50),
 SecGroup VARCHAR(100),
 Access VARCHAR(20))

INSERT INTO #tblAccess values ('ABC','AbcReturs','Admin','Modify')
INSERT INTO #tblAccess values ('ABC','AbcReturs','DatabaseAdmin','Modify')
INSERT INTO #tblAccess values ('ABC','AbcReturs','Accounts','Read')
INSERT INTO #tblAccess values ('ABC','AbcReturs','Clients','Read')
INSERT INTO #tblAccess values ('ABC','AbcReturs','Documents','Modify')
INSERT INTO #tblAccess values ('XYZ','ResiReturns','Admin','Modify')
INSERT INTO #tblAccess values ('XYZ','ResiReturns','DatabaseAdmin','Modify')
INSERT INTO #tblAccess values ('XYZ','ResiReturns','Accounts','Read')
INSERT INTO #tblAccess values ('XYZ','ResiReturns','Clients','Read')
INSERT INTO #tblAccess values ('XYZ','ResiReturns','Documents','Modify')
INSERT INTO #tblAccess values ('XYZ','TrdReturns','Admin','Modify')
INSERT INTO #tblAccess values ('XYZ','TrdReturns','DatabaseAdmin','Modify')
INSERT INTO #tblAccess values ('XYZ','TrdReturns','Accounts','Read')
INSERT INTO #tblAccess values ('XYZ','TrdReturns','Clients','Read')
INSERT INTO #tblAccess values ('XYZ','TrdReturns','Documents','Modify')
INSERT INTO #tblAccess values ('PQR','Lords','Admin','Modify')
INSERT INTO #tblAccess values ('PQR','Lords','DatabaseAdmin','Modify')
INSERT INTO #tblAccess values ('PQR','Lords','Accounts','Read')

INSERT INTO #tblAccess values ('PQR','Lords','Clients','Modify')    ---
INSERT INTO #tblAccess values ('PQR','Lords','Documents','Read')    ---
INSERT INTO #tblAccess values ('PQR','Lords','Correspondence','Modify')    ---

The Output should be:

PQR - Lords - Clients - Modify - MISMATCH
PQR - Lords - Documents - Read - MISMATCH
PQR - Lords - Correspondence - Modify - MISMATCH (Does not exist in master table)

Thanks !

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-05 : 15:14:45

If you don't need the type of mismatch, use this:

SELECT
	a.*
FROM
	#tblAccess a
WHERE
	NOT EXISTS
	(
		SELECT * FROM #tblGrpMaster m
		WHERE m.SecGroup = a.SecGroup
		AND m.Access = a.Access
	)

If you also need to know whether it does not exist in the master table, then use this:

SELECT
	a.*,
	CASE 
		WHEN b.SecGroup IS NULL THEN 'MISMATCH (Does not exist in master table)'
		ELSE 'MISMATCH'
	END
FROM
	#tblAccess a
	LEFT JOIN
	(SELECT DISTINCT SecGroup FROM #tblGrpMaster ) b
		ON b.SecGroup = a.SecGroup
WHERE
	NOT EXISTS
	(
		SELECT * FROM #tblGrpMaster m
		WHERE m.SecGroup = a.SecGroup
		AND m.Access = a.Access
	)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-05 : 15:18:14

SELECT *
FROM #tblAccess a
INNER JOIN
(
SELECT SecGroup,Access
FROM #tblAccess
EXCEPT
SELECT SecGroup,Access
FROM #tblGrpmaster
) b ON a.SecGroup = b.Secgroup and a.Access = b.Access

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-05 : 15:20:26

Or even just this

SELECT a.*
FROM #tblAccess a
LEFT JOIN #tblGrpmaster g on a.SecGroup = g.SecGroup and a.Access = g.Access
where g.Access is null or g.secgroup is null

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources