Menu
I have 3 Tables: NotHeard,analyzed,analyzed2. In each of these tables I have two columns named UnitID and Address.
What I'm trying to do right now is to select all of the records for the columns UnitID and Address from NotHeard that don't appear in either analyzed or analyzed2. The SQL statement I created was as follows:
SELECT UnitID, Address
INTO [NotHeardByEither]
FROM [NotHeard]
Where NOT EXISTS( Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID)
or NOT EXISTS( Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID)
Group BY UnitID, Address
I thought this would work since I've used the single NOT EXISTS subquery line and it has worked just fine for me in the past. The above query however returns the same data that is in the NotHeard table whereas if I take out the or NOT EXISTS part it works correctly.
Any ideas as to what I'm doing wrong or how to do what I'm wanting to do?
880
You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators.
But yes, you can use two WHERE.
If EXISTS (subquery) returns at least 1 row, the result is TRUE. If EXISTS (subquery) returns no rows, the result is FALSE. If NOT EXISTS (subquery) returns at least 1 row, the result is FALSE. If NOT EXISTS (subquery) returns no rows, the result is TRUE.
Syntax: SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name(s) FROM table_name WHERE condition);
Regarding you original query, try
NOT (
EXISTS(
Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID)
AND EXISTS(
Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID)
)
which would mean not in either. But that's equal to what you had originally (tested on sample data). Are you sure that you don't mean not in A neither in B? That would be
NOT (
EXISTS(
Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID)
OR EXISTS(
Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID)
)
Do realize that the EXISTS solution is using correlated subqueries which might perform worse then LEFT JOIN and NULLs, here's a sample.
SELECT NotHeard.UnitID, NotHeard.Address
FROM (NotHeard LEFT JOIN analyzed ON NotHeard.UnitID = analyzed.UnitID)
LEFT JOIN analyzed2 ON NotHeard.UnitID = analyzed2.UnitID
WHERE analyzed.UnitID Is Null OR analyzed2.UnitID Is Null
GROUP BY NotHeard.UnitID, NotHeard.Address;
Notice that I used OR in the condition as compared to Austin's solution, AND would give you neither in analyzed nor in analyzed2.
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
