SQL SELECT syntax for finding "missing" records?

Posted on 2009-02-20
Last Modified: 2012-05-06
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.
Question by:colinasad
    LVL 142

    Accepted Solution

    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

    LVL 1

    Expert Comment

    try this

    select * from order where stockcode not in (select stock code from stock)

    Author Closing Comment

    Thanks, angelIII, for a very prompt solution.

    Author Comment

    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.
    LVL 1

    Expert Comment

    its ok,your problem is now cleared.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video discusses moving either the default database or any database to a new volume.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now