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 |
k3n51mm
Starting Member
4 Posts |
Posted - 2008-11-12 : 19:44:09
|
The following pseudoquery is ANSI SQL, but will not work inMS SQL because I cannot use multiple columns in a NOT IN subquery. How can I get this result set using Transact-SQL? I keep gettinglost trying to figure it out.SELECT Field1, Field2, Field3, Field4 FROM Table1WHERE Field5 = 'Param1'AND Field1, Field2 NOT IN(SELECT Field1, Field2 FROM Table2 WHERE Field6 = 'Param2')Thanks |
|
singularity
Posting Yak Master
153 Posts |
Posted - 2008-11-12 : 20:02:30
|
SELECT t1.Field1, t1.Field2, t1.Field3, t1.Field4FROM Table1 t1LEFT JOIN (SELECT Field1, Field2FROM Table2WHERE Field6 = 'Param2') t2 ON t1.Field1 = t2.Field1 and t1.Field2 = t2.Field2WHERE t1.Field5 = 'Param1' AND t2.Field1 IS NULL |
|
|
k3n51mm
Starting Member
4 Posts |
Posted - 2008-11-12 : 20:38:37
|
Thanks, but we received this error when running your query:'There was an error parsing the query. [ Token line number = 3,Token line offset = 12,Token in error = SELECT ]'Here's what we had tried before, but it didn't seem to be working. It seems to be a garbled version of yours:SELECT t1.Field1, t1.Field2, t1.Field3, t1.Field4 FROM Table1 t1 LEFT JOIN Table2 t2 ON(t1.Field2 = t2.Field2) AND (t1.Field1 = t2.Field1) AND (t2.Field5 = 'Param1') WHERE (t1.Field6 = 'Param2') AND t2.Field1 IS NULLAny further ideas are appreciated. |
|
|
Jason100
Starting Member
34 Posts |
Posted - 2008-11-12 : 22:15:16
|
SELECT a.Field1, a.Field2, a.Field3, a.Field4 FROM Table1 aWHERE Field5 = 'Param1'AND NOT EXISTS (SELECT * FROM Table2 b WHERE a.Field1=b.Field1 AND a.Field2 = b.Field2 AND Field6 = 'Param2') |
|
|
k3n51mm
Starting Member
4 Posts |
Posted - 2008-11-12 : 22:32:50
|
I think that's it, THANK YOU! |
|
|
|
|
|
|
|