How do you do a WHERE NOT IN using multiple columns?
-
This is a SQL SELECT question, which I am sure is relevant for all DBMS's.
I've got a simple table structure, that has just 2 columns:
ClientNumber int
CaseNumber smallint
I'll call this table Table1.
I've got a view, which links Table1 with another table, using an inner join, so that it will only return those records out of Table1 which have corresponding records in Table2. That is easy; a piece of cake. The view (I'll call it MyView) returns the same columns, ClientNumber and CaseNumber.
Now, what I want is all records in Table1 which are not in the MyView view. The SQL SELECT's WHERE clause, with the NOT IN conditional doesn't work, because that only compares one column from Table1 against a subquery or expression. I want to compare 2 columns; something like
SELECT *
FROM Table1
WHERE ClientNumber NOT IN (SELECT ClientNumber FROM MyView)
AND CaseNumber NOT IN (SELECT CaseNumber FROM MyView)
I am sure the above syntax will not work for me, but I would really like to know what would. And generally speaking how do you go about determining all records from some table which are not in some subquery comparing 2 or more columns against the subquery?
Kindest Regards, Rod Connect with me on LinkedIn.
-
Here's previous post on the same topic:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=289159
you should use a left join and check for NULL in a join column of the outer table. The alternaitive is concatenation but that is messy even though it may have some performance benefits in some circumstances.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
stax68,
I've looked at the message you referenced, but have a question. Gregory Johnson, who posted the message, used a column called colX. What's that? I don't see it anywhere in the rest of his message, so I'm not sure how to use it.
Kindest Regards, Rod Connect with me on LinkedIn.
-
Yes, it is unclear. The column needs to be a column in the outer table (i.e. the one to the right of the left join) which cannot be null. You could use any non-nullable column if you wanted, but I generally use one of the columns I am joining on, since even if the column is nullable, it can never be null for the rows which successfully join. Also, because it is already referenced in the query, there's no danger of SQL having to do extra work to retrieve extra columns.
The idea is that in an outer join, SQL pads the empty (unmatched) rows from the outer table with NULLs. So to check that no match is found, you check for a null in teh outer table. But you need to ensure that the column doesn't contain a real NULL value from the data in the outer table.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Try this one
SELECT *
FROM Table1
WHERE not exists (SELECT 1 FROM MyView where Table1.ClientNumber=MyView.ClientNumber and Table1.CaseNumber=MyView.CaseNumber )
As you can imagine this can be extended to n columns.
Claudia
-
Thank you, Stax68 and Claudia. I appreciate your help.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply