SQL SELECT syntax for finding "missing" records?

Can someone help me with the SQL SELECT syntax (that I'm sure must be available), to find order entry records with "orphaned" stock codes?

ie I want find the order entry records whose "stock code" no longer exists in the master stock table.

I have tried various approaches and can't seem to nail it.
colinasadAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this will do:
select * 
from stock
left join master
  on master.stock_code = stock.stock_code
where master.stock_code is null

Open in new window

0
 
prabhuyogiCommented:
try this

select * from order where stockcode not in (select stock code from stock)
0
 
colinasadAuthor Commented:
Thanks, angelIII, for a very prompt solution.
Colin.
0
 
colinasadAuthor Commented:
Sorry, prachuyoqi,
Your suggestion only appeared when my screen refreshed after I posted my acceptance of angelIII's solution.
I have just tested your suggestion and it works for me too!
Many thanks.
0
 
prabhuyogiCommented:
its ok,your problem is now cleared.

Thanks

Prabhu.M
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.