Selecting records with similar values...

[fdgsogc]

I have a table of user permissions for project records. I want to be able to select the users that share permissions for the same projects for a selected list of users.

Here is an example of the record set for the permissions table.

USERID    PROJECTID
  5           43
  5           44
  5           45
  6           43
  6           45
  7           43
  7           45
  7           50

I want a select statement where the userid is '5' or '6' that will only pick projectids '43' and '45' since userid '6' doesn't have access to projectid '44'.

Thanks for any help you can provide.

 

[bborissov]

SELECT YourTable.*
FROM YourTable
INNER JOIN YourTable Ytb ON YourTable.ProjectId = Ytb.ProjectId AND
           YTb.UserId = 6
WHERE YourTable.UserId = 5
UNION ALL
SELECT YourTable.*
FROM YourTable
INNER JOIN YourTable Ytb ON YourTable.ProjectId = Ytb.ProjectId AND
           YTb.UserId = 5
WHERE YourTable.UserId = 6

NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PDreyer]

select * from tab1 a 
where USERID in (5,6)
  and exists 
(select 1 from tab1 b 
 where a.USERID!=b.USERID
   and a.PROJECTID=b.PROJECTID)