SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 multiple columns in a NOT IN subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

k3n51mm
Starting Member

4 Posts

Posted - 11/12/2008 :  19:44:09  Show Profile  Reply with Quote
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

151 Posts

Posted - 11/12/2008 :  20:02:30  Show Profile  Reply with Quote
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 - 11/12/2008 :  20:38:37  Show Profile  Reply with Quote
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 - 11/12/2008 :  22:15:16  Show Profile  Reply with Quote
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 - 11/12/2008 :  22:32:50  Show Profile  Reply with Quote
I think that's it, THANK YOU!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000