April 17, 2002 at 7:08 pm
I want to find all entries in one table that are not in another.
The query is below
SELECT *
FROM Figtree a
Where VehicleNo not in (Select Figtree_id from Vehicle)
It returns no rows even though there are rows that dont match.
The alternative not exists syntax works however.
SELECT *
FROM Figtree a
Where not exists (Select * from Vehicle where VehicleNo = Figtree_id )
Anybody else seen this?
April 18, 2002 at 4:53 am
Yes, an it can be a pain to figure out exactly why. Can you post the DDL of both tables please.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 18, 2002 at 8:48 am
Maybe this would work
SELECT *
FROM Figtree a
left join VehicleNo b.Figtree_id
Where b.Figtree_id is null
I want to find all entries in one table that are not in another.
The query is below
SELECT *
FROM Figtree a
Where VehicleNo not in (Select Figtree_id from Vehicle)
It returns no rows even though there are rows that dont match.
The alternative not exists syntax works however.
SELECT *
FROM Figtree a
Where not exists (Select * from Vehicle where VehicleNo = Figtree_id )
Anybody else seen this?
[/quote]
April 18, 2002 at 9:20 am
Sorry posted my last reply in error.
I think this would work.
select a.*
from Figtree a
left join Vehicle b
on a.VehicleNo = b.Figtree_id
where b.Figtree_id is null
I try never to do a sub select if I can use a Join to do the same thing.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy