• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 821
  • Last Modified:

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.
0
colinasad
Asked:
colinasad
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now