|
|
You're in the
sql server programming
group:Count matches in threesome
sql server programming:
I cannot figure out my query. Here is what the tables look like in a simplistic way. PermDefs: PermDefID PermTitle 1 Make changes to w 2 Make changes to x 3 Make changes to y 4 Make changes to z ADUsers: SID ADFullname aaa Joe Smith bbb Sally Jones ccc Kyle Korver Perms: SID PermDefID aaa 1 aaa 3 aaa 4 bbb 1 Now, what that means is that there is a list of options available to give to users, and these options are on the PermDefs table. The ADUsers table holds a list of users. And the third table, Perms, indicates which options exist for each user. What I'd like to return in my query is a list of all options that exist, and whether or not the user that I specify has that option, for example: Options for user with SID 'aaa': PermDefID PermTitle Enabled 1 Make changes to w 1 2 Make changes to x 0 3 Make changes to y 1 4 Make changes to z 1 This user has matches in the Perms table for PermDefs 1, 3, and 4, so for the Enabled column, that would be true, not-null, a count, or anything else that would indicate a match. But I would still like to see options that the user does not have. I've tried left outer joins, CASE WHEN, and everything else. I have found that I can get what I'm after by using a UNION query, but something tells me that I could do this better. This query returns the information that I'm after: DECLARE @SID CHAR(3) SET @SID='aaa' SELECT PermDefs.PermDefID,PermDefs.PermTitle,0 As Enabled FROM PermDefs INNER JOIN Perms ON PermDefs.PermDefID=Perms.PermDefID INNER JOIN ADUsers ON Perms.SID=ADUsers.SID WHERE Perms.PermDefID NOT IN (SELECT PermDefID FROM Perms WHERE SID=@SID) UNION SELECT PermDefs.PermDefID,PermDefs.PermTitle,1 As Enabled FROM PermDefs INNER JOIN Perms ON PermDefs.PermDefID=Perms.PermDefID INNER JOIN ADUsers ON Perms.SID=ADUsers.SID WHERE Perms.SID=@SID The SID column is actually 56 characters, but for the sake of illustration, I made it three. Thanks for any tips on efficiency or better querying. Ray at home
Does this work for you?
select PermDefs.PermDefID, PermDefs.PermTitle, count(Perms.PermDefID) as Enabled from PermDefs left outer join Perms on Perms.PermDefID = PermDefs.PermDefID where @sid = Perms.SID group by PermDefs.PermDefID, PermDefs.Title -- Steve Kass -- Drew University -- Ref: 959AD87C-F484-4090-93E7-429C426C4C06 [quoted text, click to view] Ray at <%=sLocation%> wrote:
>Hi group, > >I cannot figure out my query. Here is what the tables look like in a >simplistic way. > > >PermDefs: >PermDefID PermTitle >1 Make changes to w >2 Make changes to x >3 Make changes to y >4 Make changes to z > > >ADUsers: >SID ADFullname >aaa Joe Smith >bbb Sally Jones >ccc Kyle Korver > > >Perms: >SID PermDefID >aaa 1 >aaa 3 >aaa 4 >bbb 1 > > > >Now, what that means is that there is a list of options available to give to >users, and these options are on the PermDefs table. The ADUsers table holds >a list of users. And the third table, Perms, indicates which options exist >for each user. What I'd like to return in my query is a list of all options >that exist, and whether or not the user that I specify has that option, for >example: > >Options for user with SID 'aaa': > >PermDefID PermTitle Enabled >1 Make changes to w 1 >2 Make changes to x 0 >3 Make changes to y 1 >4 Make changes to z 1 > > >This user has matches in the Perms table for PermDefs 1, 3, and 4, so for >the Enabled column, that would be true, not-null, a count, or anything else >that would indicate a match. But I would still like to see options that the >user does not have. I've tried left outer joins, CASE WHEN, and everything >else. I have found that I can get what I'm after by using a UNION query, >but something tells me that I could do this better. This query returns the >information that I'm after: > >DECLARE @SID CHAR(3) >SET @SID='aaa' >SELECT PermDefs.PermDefID,PermDefs.PermTitle,0 As Enabled > FROM PermDefs INNER JOIN Perms ON PermDefs.PermDefID=Perms.PermDefID > INNER JOIN ADUsers ON Perms.SID=ADUsers.SID > WHERE Perms.PermDefID NOT IN > (SELECT PermDefID FROM Perms WHERE SID=@SID) > >UNION > >SELECT PermDefs.PermDefID,PermDefs.PermTitle,1 As Enabled > FROM PermDefs INNER JOIN Perms ON PermDefs.PermDefID=Perms.PermDefID > INNER JOIN ADUsers ON Perms.SID=ADUsers.SID > WHERE Perms.SID=@SID > > >The SID column is actually 56 characters, but for the sake of illustration, >I made it three. > >Thanks for any tips on efficiency or better querying. > >Ray at home > > > > > > > > > >
Hi Steve,
Thanks. That one also just returns the info where there are matches, for example: 1 Make changes to w 1 3 Make changes to y 1 4 Make changes to z 1 It doesn't return 2 with a count of 0. Thanks, Ray at home [quoted text, click to view] "Steve Kass" <skass@drew.edu> wrote in message news:%23IM3dN$qDHA.708@TK2MSFTNGP10.phx.gbl... > Does this work for you? > > select > PermDefs.PermDefID, > PermDefs.PermTitle, > count(Perms.PermDefID) as Enabled > from PermDefs > left outer join Perms > on Perms.PermDefID = PermDefs.PermDefID > where @sid = Perms.SID > group by PermDefs.PermDefID, PermDefs.Title > > -- Steve Kass > -- Drew University > -- Ref: 959AD87C-F484-4090-93E7-429C426C4C06 > > > Ray at <%=sLocation%> wrote: > > >Hi group, > > > >I cannot figure out my query. Here is what the tables look like in a > >simplistic way. > > > > > >PermDefs: > >PermDefID PermTitle > >1 Make changes to w > >2 Make changes to x > >3 Make changes to y > >4 Make changes to z > > > > > >ADUsers: > >SID ADFullname > >aaa Joe Smith > >bbb Sally Jones > >ccc Kyle Korver > > > > > >Perms: > >SID PermDefID > >aaa 1 > >aaa 3 > >aaa 4 > >bbb 1 > > > > > > > >Now, what that means is that there is a list of options available to give to > >users, and these options are on the PermDefs table. The ADUsers table holds > >a list of users. And the third table, Perms, indicates which options exist > >for each user. What I'd like to return in my query is a list of all options > >that exist, and whether or not the user that I specify has that option, for > >example: > > > >Options for user with SID 'aaa': > > > >PermDefID PermTitle Enabled > >1 Make changes to w 1 > >2 Make changes to x 0 > >3 Make changes to y 1 > >4 Make changes to z 1 > > > > > >This user has matches in the Perms table for PermDefs 1, 3, and 4, so for > >the Enabled column, that would be true, not-null, a count, or anything else > >that would indicate a match. But I would still like to see options that the > >user does not have. I've tried left outer joins, CASE WHEN, and everything > >else. I have found that I can get what I'm after by using a UNION query, > >but something tells me that I could do this better. This query returns the > >information that I'm after: > > > >DECLARE @SID CHAR(3) > >SET @SID='aaa' > >SELECT PermDefs.PermDefID,PermDefs.PermTitle,0 As Enabled > > FROM PermDefs INNER JOIN Perms ON PermDefs.PermDefID=Perms.PermDefID > > INNER JOIN ADUsers ON Perms.SID=ADUsers.SID > > WHERE Perms.PermDefID NOT IN > > (SELECT PermDefID FROM Perms WHERE SID=@SID) > > > >UNION > > > >SELECT PermDefs.PermDefID,PermDefs.PermTitle,1 As Enabled > > FROM PermDefs INNER JOIN Perms ON PermDefs.PermDefID=Perms.PermDefID > > INNER JOIN ADUsers ON Perms.SID=ADUsers.SID > > WHERE Perms.SID=@SID > > > > > >The SID column is actually 56 characters, but for the sake of illustration, > >I made it three. > > > >Thanks for any tips on efficiency or better querying. > > > >Ray at home > > > > > > > > > > > > > > > > > > > > >
Wouldn't you want the @sid in the join clause, not the Where?
Select * Into #PermDefs From ( Select 1 As PermDefID, 'Make changes to w' AS PermTitle UNION ALL Select 2, 'Make changes to x' UNION ALL Select 3, 'Make changes to y' UNION ALL Select 4, 'Make changes to z' ) As x Select * Into #ADUsers From ( Select 'aaa' As SID, 'Joe Smith' AS ADFullname UNION ALL Select 'bbb', 'Sally Jones' UNION ALL Select 'ccc', 'Kyle Korver' ) As x Select * Into #Perms From ( Select 'aaa' As SID, 1 AS PermDefID UNION ALL Select 'aaa', 3 UNION ALL Select 'aaa', 4 UNION ALL Select 'bbb', 1 ) As x select pd.PermDefID, pd.PermTitle, count(p.PermDefID) as Enabled from #PermDefs As pd left outer join #Perms As p on p.PermDefID = pd.PermDefID and @sid = p.SID group by pd.PermDefID, pd.PermTitle Drop Table #PermDefs Drop Table #ADUsers Drop Table #Perms [quoted text, click to view] "Steve Kass" <skass@drew.edu> wrote in message news:#IM3dN$qDHA.708@TK2MSFTNGP10.phx.gbl... > Does this work for you? > > select > PermDefs.PermDefID, > PermDefs.PermTitle, > count(Perms.PermDefID) as Enabled > from PermDefs > left outer join Perms > on Perms.PermDefID = PermDefs.PermDefID > where @sid = Perms.SID > group by PermDefs.PermDefID, PermDefs.Title > > -- Steve Kass > -- Drew University > -- Ref: 959AD87C-F484-4090-93E7-429C426C4C06 > > > Ray at <%=sLocation%> wrote: > > >Hi group, > > > >I cannot figure out my query. Here is what the tables look like in a > >simplistic way. > > > > > >PermDefs: > >PermDefID PermTitle > >1 Make changes to w > >2 Make changes to x > >3 Make changes to y > >4 Make changes to z > > > > > >ADUsers: > >SID ADFullname > >aaa Joe Smith > >bbb Sally Jones > >ccc Kyle Korver > > > > > >Perms: > >SID PermDefID > >aaa 1 > >aaa 3 > >aaa 4 > >bbb 1 > > > > > > > >Now, what that means is that there is a list of options available to give to > >users, and these options are on the PermDefs table. The ADUsers table holds > >a list of users. And the third table, Perms, indicates which options exist > >for each user. What I'd like to return in my query is a list of all options > >that exist, and whether or not the user that I specify has that option, for > >example: > > > >Options for user with SID 'aaa': > > > >PermDefID PermTitle Enabled > >1 Make changes to w 1 > >2 Make changes to x 0 > >3 Make changes to y 1 > >4 Make changes to z 1 > > > > > >This user has matches in the Perms table for PermDefs 1, 3, and 4, so for > >the Enabled column, that would be true, not-null, a count, or anything else > >that would indicate a match. But I would still like to see options that the > >user does not have. I've tried left outer joins, CASE WHEN, and everything > >else. I have found that I can get what I'm after by using a UNION query, > >but something tells me that I could do this better. This query returns the > >information that I'm after: > > > >DECLARE @SID CHAR(3) > >SET @SID='aaa' > >SELECT PermDefs.PermDefID,PermDefs.PermTitle,0 As Enabled > > FROM PermDefs INNER JOIN Perms ON PermDefs.PermDefID=Perms.PermDefID > > INNER JOIN ADUsers ON Perms.SID=ADUsers.SID > > WHERE Perms.PermDefID NOT IN > > (SELECT PermDefID FROM Perms WHERE SID=@SID) > > > >UNION > > > >SELECT PermDefs.PermDefID,PermDefs.PermTitle,1 As Enabled > > FROM PermDefs INNER JOIN Perms ON PermDefs.PermDefID=Perms.PermDefID > > INNER JOIN ADUsers ON Perms.SID=ADUsers.SID > > WHERE Perms.SID=@SID > > > > > >The SID column is actually 56 characters, but for the sake of illustration, > >I made it three. > > > >Thanks for any tips on efficiency or better querying. > > > >Ray at home > > > > > > > > > > > > > > > > > > > > >
Select *
Into #PermDefs From ( Select 1 As PermDefID, 'Make changes to w' AS PermTitle UNION ALL Select 2, 'Make changes to x' UNION ALL Select 3, 'Make changes to y' UNION ALL Select 4, 'Make changes to z' ) As x Select * Into #ADUsers From ( Select 'aaa' As SID, 'Joe Smith' AS ADFullname UNION ALL Select 'bbb', 'Sally Jones' UNION ALL Select 'ccc', 'Kyle Korver' ) As x Select * Into #Perms From ( Select 'aaa' As SID, 1 AS PermDefID UNION ALL Select 'aaa', 3 UNION ALL Select 'aaa', 4 UNION ALL Select 'bbb', 1 ) As x Select x.PermDefID, x.PermTitle, x.SID, x.ADFullname, Case When p.SID Is Null Then 0 Else 1 End As Enabled From (Select d.PermDefID, d.PermTitle, u.SID, u.ADFullname From #PermDefs As d Cross Join #ADUsers AS u) AS x Left Join #Perms As p On x.PermDefID=p.PermDefID And x.SID=p.SID Drop Table #PermDefs Drop Table #ADUsers Drop Table #Perms
Yup. Sorry for the slip.
SK [quoted text, click to view] Robert Paquette wrote:
>Wouldn't you want the @sid in the join clause, not the Where? > >Select * >Into #PermDefs >From >( >Select 1 As PermDefID, 'Make changes to w' AS PermTitle >UNION ALL >Select 2, 'Make changes to x' >UNION ALL >Select 3, 'Make changes to y' >UNION ALL >Select 4, 'Make changes to z' >) As x > >Select * >Into #ADUsers >From >( >Select 'aaa' As SID, 'Joe Smith' AS ADFullname >UNION ALL >Select 'bbb', 'Sally Jones' >UNION ALL >Select 'ccc', 'Kyle Korver' >) As x > >Select * >Into #Perms >From >( >Select 'aaa' As SID, 1 AS PermDefID >UNION ALL >Select 'aaa', 3 >UNION ALL >Select 'aaa', 4 >UNION ALL >Select 'bbb', 1 >) As x > >select > pd.PermDefID, > pd.PermTitle, > count(p.PermDefID) as Enabled >from #PermDefs As pd > left outer join #Perms As p >on p.PermDefID = pd.PermDefID and @sid = p.SID >group by pd.PermDefID, pd.PermTitle > >Drop Table #PermDefs >Drop Table #ADUsers >Drop Table #Perms > > >"Steve Kass" <skass@drew.edu> wrote in message >news:#IM3dN$qDHA.708@TK2MSFTNGP10.phx.gbl... > > >>Does this work for you? >> >>select >> PermDefs.PermDefID, >> PermDefs.PermTitle, >> count(Perms.PermDefID) as Enabled >>from PermDefs >>left outer join Perms >>on Perms.PermDefID = PermDefs.PermDefID >>where @sid = Perms.SID >>group by PermDefs.PermDefID, PermDefs.Title >> >>-- Steve Kass >>-- Drew University >>-- Ref: 959AD87C-F484-4090-93E7-429C426C4C06 >> >> >>Ray at <%=sLocation%> wrote: >> >> >> >>>Hi group, >>> >>>I cannot figure out my query. Here is what the tables look like in a >>>simplistic way. >>> >>> >>>PermDefs: >>>PermDefID PermTitle >>>1 Make changes to w >>>2 Make changes to x >>>3 Make changes to y >>>4 Make changes to z >>> >>> >>>ADUsers: >>>SID ADFullname >>>aaa Joe Smith >>>bbb Sally Jones >>>ccc Kyle Korver >>> >>> >>>Perms: >>>SID PermDefID >>>aaa 1 >>>aaa 3 >>>aaa 4 >>>bbb 1 >>> >>> >>> >>>Now, what that means is that there is a list of options available to give >>> >>> >to > > >>>users, and these options are on the PermDefs table. The ADUsers table >>> >>> >holds > > >>>a list of users. And the third table, Perms, indicates which options >>> >>> >exist > > >>>for each user. What I'd like to return in my query is a list of all >>> >>> >options > > >>>that exist, and whether or not the user that I specify has that option, >>> >>> >for > > >>>example: >>> >>>Options for user with SID 'aaa': >>> >>>PermDefID PermTitle Enabled >>>1 Make changes to w 1 >>>2 Make changes to x 0 >>>3 Make changes to y 1 >>>4 Make changes to z 1 >>> >>> >>>This user has matches in the Perms table for PermDefs 1, 3, and 4, so for >>>the Enabled column, that would be true, not-null, a count, or anything >>> >>> >else > > >>>that would indicate a match. But I would still like to see options that >>> >>> >the > > >>>user does not have. I've tried left outer joins, CASE WHEN, and >>> >>> >everything > > >>>else. I have found that I can get what I'm after by using a UNION query, >>>but something tells me that I could do this better. This query returns >>> >>> >the > > >>>information that I'm after: >>> >>>DECLARE @SID CHAR(3) >>>SET @SID='aaa' >>>SELECT PermDefs.PermDefID,PermDefs.PermTitle,0 As Enabled >>>FROM PermDefs INNER JOIN Perms ON PermDefs.PermDefID=Perms.PermDefID >>>INNER JOIN ADUsers ON Perms.SID=ADUsers.SID >>>WHERE Perms.PermDefID NOT IN >>> (SELECT PermDefID FROM Perms WHERE SID=@SID) >>> >>>UNION >>> >>>SELECT PermDefs.PermDefID,PermDefs.PermTitle,1 As Enabled >>>FROM PermDefs INNER JOIN Perms ON PermDefs.PermDefID=Perms.PermDefID >>>INNER JOIN ADUsers ON Perms.SID=ADUsers.SID >>>WHERE Perms.SID=@SID >>> >>> >>>The SID column is actually 56 characters, but for the sake of >>> >>> >illustration, > > >>>I made it three. >>> >>>Thanks for any tips on efficiency or better querying. >>> >>>Ray at home >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> > > > >
select
pd.PermDefID, pd.PermTitle, count(p.PermDefID) as Enabled from PermDefs As pd left outer join Perms As p on p.PermDefID = pd.PermDefID and @sid = p.SID group by pd.PermDefID, pd.PermTitle That's working perfectly. Thanks a lot Robert. Ray at home [quoted text, click to view] "Robert Paquette" <robert@SPAMGUARDpaquettesoftware.com> wrote in message [trim]news:ud0hLZ$qDHA.2520@TK2MSFTNGP09.phx.gbl... > Wouldn't you want the @sid in the join clause, not the Where? > > Select * > Into #PermDefs > From > ( > Select 1 As PermDefID, 'Make changes to w' AS PermTitle > UNION ALL > Select 2, 'Make changes to x' > UNION ALL > Select 3, 'Make changes to y' > UNION ALL > Select 4, 'Make changes to z' > ) As x > > Select * > Into #ADUsers > From > ( > Select 'aaa' As SID, 'Joe Smith' AS ADFullname > UNION ALL > Select 'bbb', 'Sally Jones' > UNION ALL > Select 'ccc', 'Kyle Korver' > ) As x > > Select * > Into #Perms > From > ( > Select 'aaa' As SID, 1 AS PermDefID > UNION ALL > Select 'aaa', 3 > UNION ALL > Select 'aaa', 4 > UNION ALL > Select 'bbb', 1 > ) As x > > select > pd.PermDefID, > pd.PermTitle, > count(p.PermDefID) as Enabled > from #PermDefs As pd > left outer join #Perms As p > on p.PermDefID = pd.PermDefID and @sid = p.SID > group by pd.PermDefID, pd.PermTitle > > Drop Table #PermDefs > Drop Table #ADUsers > Drop Table #Perms > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |