SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Compare Data SQL Query help ....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqldba20
Posting Yak Master

183 Posts

Posted - 12/05/2012 :  14:50:25  Show Profile  Reply with Quote
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 !


Edited by - sqldba20 on 12/05/2012 14:52:03

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/05/2012 :  15:14:45  Show Profile  Reply with Quote
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
	)
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/05/2012 :  15:18:14  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/05/2012 :  15:20:26  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000