September 12, 2012 at 12:38 pm
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
September 12, 2012 at 12:44 pm
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
September 12, 2012 at 6:22 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 13, 2012 at 8:01 am
The goal is to find out which permissions one group has that another group does not, or vise verse.
September 13, 2012 at 10:32 am
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?
September 13, 2012 at 10:38 am
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.
September 13, 2012 at 1:27 pm
I will be comparing only two groups at a time.
September 13, 2012 at 1:29 pm
That looks good. As long as I can identify which permissions differ between two groups.
September 13, 2012 at 4:16 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 14, 2012 at 8:36 am
Worked perfect! Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply