469,138 Members | 1,283 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

Select record where foreign record does not exist.

I'm stumped on this one, so here goes....

I have 2 tables project and assignment

they are linked by the field proj_id where proj_id is the primary key of project and a foreign key in assignment.

I now have a situation where there might not be a record in assignment.

need to figure out how to get a list of proj_id's where there is no corresponding record in assignment.

I hope I have explained this clearly enough

thanks for any help in advance
Jul 17 '07 #1
3 6939
I'm stumped on this one, so here goes....

I have 2 tables project and assignment

they are linked by the field proj_id where proj_id is the primary key of project and a foreign key in assignment.

I now have a situation where there might not be a record in assignment.

need to figure out how to get a list of proj_id's where there is no corresponding record in assignment.

I hope I have explained this clearly enough

thanks for any help in advance
Select proj_id
From project
Where proj_id not in (select proj_id from assignment)
Jul 18 '07 #2
Thanks!

had a sneaky feeling it was going to be simple
Jul 18 '07 #3
DonlonP
25
Select proj_id
From project
Where proj_id not in (select proj_id from assignment)

A more efficient way would be:

SELECT p.proj_id
FROM project p
LEFT JOIN assignment a
ON p.proj_id = a.proj_id
WHERE a.proj_id IS NULL
Jul 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by jayson_13 | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.