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