March 22, 2006 at 8:35 am
Any suggestions for the following
I'm able to run it with one NOT IN statement, but having trouble with the second.
Should I create a temp table with both sets of data or is it possibly in a way like this?
Assume
tblNumbers has (1,2,3,4,5 & 6) for records
tblNumA has (1,2) for records
tblNumB has (3,4) for records
I'd like to return 5 & 6 for the result
------------------------------------------------
SELECT * FROM tblNumbers
WHERE
RecNum NOT IN (SELECT SomeNum FROM tblNumA)
AND
RecNum NOT IN (SELECT SomeNum FROM tblNumB)
------------------------------------------------
This returns 0 results, but if i remove the "AND ......" it works fine for the first table, but I need to return the results using both tables.
This will eventually end up as a "DELETE FROM ....." query
Your suggestions are greatly appreciated.
Rick
March 22, 2006 at 8:51 am
My Mistake,
I had the same data in each table, returning 0 results.
I've since added data NOT IN the other tables and it worked fine
March 22, 2006 at 8:55 am
DECLARE @tblNumbers TABLE (ID int)
INSERT INTO @tblNumbers VALUES (1)
INSERT INTO @tblNumbers VALUES (2)
INSERT INTO @tblNumbers VALUES (3)
INSERT INTO @tblNumbers VALUES (4)
INSERT INTO @tblNumbers VALUES (5)
INSERT INTO @tblNumbers VALUES (6)
DECLARE @tblNumA TABLE(ID int)
INSERT INTO @tblNumA VALUES (1)
INSERT INTO @tblNumA VALUES (2)
DECLARE @tblNumB TABLE(ID int)
INSERT INTO @tblNumB VALUES (3)
INSERT INTO @tblNumB VALUES (4)
SELECT * FROM @tblNumbers
WHERE
ID NOT IN (SELECT ID FROM @tblNumA UNION SELECT ID FROM @tblNumB)
March 22, 2006 at 8:56 am
I found that this also worked faster (suggestions/comments are welcome too)
SELECT * FROM tblNumbers
WHERE
RecNum NOT IN
(
SELECT SomeNum FROM tblNumA
UNION
SELECT SomeNum FROM tblNumB
)
March 22, 2006 at 8:58 am
Thanks SQL Junkie,
Looks like the postings crossed paths
Thanks for the reply
March 23, 2006 at 11:36 am
Just for kicks, here's an alternate method using JOINS:
SELECT n.*
FROM @tblNumbers n
LEFT JOIN @tblNumA a
ON n.ID = a.ID
LEFT JOIN @tblNumB b
ON n.ID = b.ID
WHERE a.ID Is Null
AND b.ID Is Null
- Paul
http://paulpaivasql.blogspot.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply