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 |
|
Jomypgeorge
Starting Member
31 Posts |
 Posted - 2010-08-10 : 09:26:12
|
| hi alli have a table for storing computer permissions. as belowcreate table #testtable (machine nvarchar(50),access nvarchar(50))insert into #testtable values ('a','read')insert into #testtable values ('a','write')insert into #testtable values ('a','append')insert into #testtable values ('b','read')insert into #testtable values ('b','read')insert into #testtable values ('c','write')insert into #testtable values ('d','append')select * from #testtabledrop table #testtablenow i have to fetch all machines without a given permission say 'write'. i did it using an inner query, but like to know the best way to get it.the query im using now is select distinct machine from #testtable where machine not in (select machine from #testtable where access = 'write') |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-10 : 09:32:53
|
I would prefer to use NOT EXISTS().But we have discussed that a few days before here in the forums and the result was: there is no difference between NOT IN() and NOT EXISTS().Also we have discussed to do a left join instead but that was a little bit slower than the other methods.So I think your query is ok.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Jomypgeorge
Starting Member
31 Posts |
Posted - 2010-08-10 : 09:39:03
|
| but how to use left outer join here?can u explian? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-10 : 10:02:28
|
select DISTINCTt1.machinefrom #testtable as t1left join #testtable as t2 on t1.machine = t2.machine and t2.access = 'write'where t2.machine is null
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Jomypgeorge
Starting Member
31 Posts |
Posted - 2010-08-11 : 01:38:13
|
| thanks webfred |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-11 : 03:39:51
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
select Query