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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 multiple columns in a NOT IN subquery

Author  Topic 

k3n51mm
Starting Member

4 Posts

Posted - 2008-11-12 : 19:44:09
The following pseudoquery is ANSI SQL, but will not work in
MS SQL because I cannot use multiple columns in a NOT IN subquery.
How can I get this result set using Transact-SQL? I keep getting
lost trying to figure it out.


SELECT Field1, Field2, Field3, Field4
FROM Table1
WHERE
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.Field4
FROM Table1 t1
LEFT JOIN (SELECT Field1, Field2
FROM Table2
WHERE Field6 = 'Param2') t2 ON t1.Field1 = t2.Field1 and t1.Field2 = t2.Field2
WHERE t1.Field5 = 'Param1' AND t2.Field1 IS NULL
Go to Top of Page

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 NULL

Any further ideas are appreciated.
Go to Top of Page

Jason100
Starting Member

34 Posts

Posted - 2008-11-12 : 22:15:16
SELECT a.Field1, a.Field2, a.Field3, a.Field4
FROM Table1 a
WHERE
Field5 = 'Param1'
AND NOT EXISTS (
SELECT * FROM Table2 b WHERE a.Field1=b.Field1
AND a.Field2 = b.Field2
AND Field6 = 'Param2')

Go to Top of Page

k3n51mm
Starting Member

4 Posts

Posted - 2008-11-12 : 22:32:50
I think that's it, THANK YOU!
Go to Top of Page
   

- Advertisement -