Say I have a link table with two fi 1

[Cocheez]

Say I have a link table with two fields, idUser and idPermission, I would like to know how to Select just the users that have a set of permissions. For instance getting just the users that have idPermission of 1, 2 and 3.

idUser idPermission
------ ------------
1 1
1 2
1 3
2 2
2 3

using the table above I would only want to return the rows for idUser 1. Using AND won't return any rows and using IN/OR returns everybody with 1, 2 or 3.

Thanks for any help guys/gals !

 

[ClaireHCM]

select A.col1 from
(select col1 from MyTable where col2 = 1) A
inner join
(select col1 from MyTable where col2 = 2) B on A.col1 = B.col1
inner join
(select col1 from MyTable where col2 = 3) C on A.col1 = C.col1

 

[checkai]

Not sure if I'm understanding you correctly...

SELECT A.staffName
FROM staff A
where a.idUser in (select idUser from LinkTablePermission)

 

[Cocheez]

Actually ClairHsu hit it on the head, thank you very much. Using an IN clause will bring back any record that has one of those values in it. I needed a statement that would bring back a record that matched only those values.

My friend Kevin also came up with this statement that does the same thing. So now we have two ways !

Select *
From tblUser
Where
EXISTS (
Select idUser
From tblUser_Permission
Where idUser = tblUser.idUser
And idPermission in (2,3,4)
Group by idUser
Having count(*) >= 3
)

 

[r937]

okay, first of all, Kevin uses 2,3,4 when the question asked for 1,2,3 -- but that's not the real problem with his solution

if you're gonna use HAVING count(*) >= 3 then this implies there could be more than 3, which implies that the same permission (2 or 3 or 4) could exist more than once, which means that if a user has three permissions and they're all, say, 2, then the EXISTS will be true, because the HAVING condition will have been met, but it will give the wrong answer, because that user does not have all the required permissions, just three 2s

so Kevin's solution is not the same, is it

here's another solution --

[tt]select always,list,the,columns,you,want
from tblUser
where exists (
select 0
from tblUser_Permission
where idUser = tblUser.idUser
and idPermission = 1
)
and exists (
select 0
from tblUser_Permission
where idUser = tblUser.idUser
and idPermission = 2
)
and exists (
select 0
from tblUser_Permission
where idUser = tblUser.idUser
and idPermission = 3
)[/tt]

rudy

http://r937.com

 

[Cocheez]

You are correct r937,

Kevin's solution would return the same results only on a table where the two fields together formed a unique value using either a primary key or other unique constraint. That way you would never return rows that have duplicate data and avoid the situation you suggested.

I guess I should have specified that on the last post.