Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gronske
Starting Member
1 Post |
 Posted - 2002-08-07 : 07:15:20
|
| I'm working with listing up folders which users have acces to. A user could have access to a folder through a groupmembership or direct access. The problem is when a user both has access direct and through a group, and the folder gets listed twice.Is there a way to: (select all folders the user has direct access to) NOT EXISTS in (select all folders the user has access to throug groupmembership)?Thanks :)gronske |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-07 : 07:33:01
|
| NOT EXISTS is a keyword.It's documented in Books Online.With Examples.Jay White{0} |
 |
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-08-07 : 10:23:39
|
| gronskeyou should decide whether you mean(select all folders with direct access) EXCEPT(select all folders with groupmembership) or(select all folders with direct access) UNION(select all folders with groupmembership) the EXCEPT operator is like NOT EXISTSfrom your description, i think you want UNIONUNION will remove duplicatesrudyhttp://rudy.ca/ |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-07 : 10:41:41
|
| Yeah, the EXCEPT keyword is a SQL-92 standard used to do Relational Difference. SQL Server doesn't support EXCEPT. The UNION operator, as a byproduct of its definition will remove the duplicates. However, if you just want to know the folders via direct access that are not also derived through a group membership, you will need to use a WHERE folder NOT IN (<correlated subquery>) or WHERE NOT EXISTS(<correlated subquery>) construct to mimic the relation difference operation that you would otherwise use EXCEPT for.I mean, crap, you can get to the same place with a SELECT DISTINCT and a JOIN on [direct access] and [groupmemebership access]. There a couple ways to skin this cat.Jay White{0} |
|
|
|
|
|
|
not exists....