Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data Comparison Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 12:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 256, Visits: 611
I have a table that contains permissions based on a group id. How would I go about building a script that would compare two groups and return only the records that do not match? Any help will be appreciated.

Example:
Group ID      Item            Permission
123 D-Report Y
123 G-Report Y
134 D-Report Y
134 G-Report N

Output:
Group ID Item Permission
134 G-Report N

Post #1358160
Posted Wednesday, September 12, 2012 12:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
the trick is to join the table agaisnt itself.
With MyCTE (GroupID,Item,Permission)
AS
(
SELECT '123','D-Report','Y' UNION ALL
SELECT '123','G-Report','Y' UNION ALL
SELECT '134','D-Report','Y' UNION ALL
SELECT '134','G-Report','N'
)

SELECT * FROM MyCTE T1
LEFT OUTER JOIN MyCTE T2
ON T1.Item = T2.Item
AND T1.GroupID = '123'
AND T2.GroupID = '134'
WHERE T1.Permission <> T2.Permission



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1358162
Posted Wednesday, September 12, 2012 6:22 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
bpowers (9/12/2012)
I have a table that contains permissions based on a group id. How would I go about building a script that would compare two groups and return only the records that do not match? Any help will be appreciated.

Example:
Group ID      Item            Permission
123 D-Report Y
123 G-Report Y
134 D-Report Y
134 G-Report N

Output:
Group ID Item Permission
134 G-Report N



Why would you not want to return the (second) record in bold in addition to or instead of the record that is returned?



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1358276
Posted Wednesday, September 12, 2012 7:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
>> I have a table that contains permissions based on a group id. How would I go about building a script that would compare two groups and return only the records [rows are not records] that do not match? <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are.

CREATE TABLE Group_Permissions
(group_id INTEGER NOT NULL,
report_name VARCHAR(10) NOT NULL,
PRIMARY KEY (group_id, report_name),
permission_flag CHAR(1) DEFAULT 'N' NOT NULL
CHECK (permission_flag IN ('Y', 'N')));

As an aside, we do not use flags in SQL; that was assembly language.

>> a script that would compare two groups and return only the records that do not match? <<

What is the importance of the flag? In determining a match?

CREATE PROCEDURE Group_Differences
(@in_grp_1 INTEGER, @in_grp_2 INTEGER)
AS
SELECT X.*
FROM
((SELECT group_id, report_name, permission_flag
FROM Group_Permissions
WHERE group_id = @in_grp_1)
UNION
(SELECT group_id, report_name, permission_flag
FROM Group_Permissions
WHERE group_id = @in_grp_2))
EXCEPT
((SELECT group_id, report_name, permission_flag
FROM Group_Permissions
WHERE group_id = @in_grp_1)
INTERSECT
(SELECT group_id, report_name, permission_flag
FROM Group_Permissions
WHERE group_id = @in_grp_2))
AS X (group_id, report_name, permission_flag0


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1358289
Posted Thursday, September 13, 2012 8:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 256, Visits: 611
The goal is to find out which permissions one group has that another group does not, or vise verse.
Post #1358580
Posted Thursday, September 13, 2012 10:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
1. How many groups you are going to compare at once?
2. If the answer to the first question is two, will you provide GroupId's for groups to compare into the query?


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1358704
Posted Thursday, September 13, 2012 10:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:55 PM
Points: 22,492, Visits: 30,199
bpowers (9/13/2012)
The goal is to find out which permissions one group has that another group does not, or vise verse.


If so, then why not:
Group ID      Item            Permission
123 D-Report Y
123 G-Report Y
134 D-Report Y
134 G-Report N

Output:
Group ID Item Permission
123 G-Report Y
134 G-Report N

The permissions are different between the two.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1358708
Posted Thursday, September 13, 2012 1:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 256, Visits: 611
I will be comparing only two groups at a time.
Post #1358820
Posted Thursday, September 13, 2012 1:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 256, Visits: 611
That looks good. As long as I can identify which permissions differ between two groups.
Post #1358822
Posted Thursday, September 13, 2012 4:16 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 1,746, Visits: 2,553
SELECT
Item,
MAX(CASE WHEN [Group ID] = @group1 THEN Permission ELSE '' END) AS Group1_Permission,
MAX(CASE WHEN [Group ID] = @group2 THEN Permission ELSE '' END) AS Group2_Permission
FROM
dbo.#work
WHERE
[Group ID] IN ( @group1, @group2 )
GROUP BY
Item
HAVING
MAX(CASE WHEN [Group ID] = @group1 THEN Permission ELSE '' END) <>
MAX(CASE WHEN [Group ID] = @group2 THEN Permission ELSE '' END)
ORDER BY
Item



SQL DBA,SQL Server MVP('07, '08, '09)
I'm not fat, I'm gravity challenged.
Post #1358949
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse