Query to Link Three Tables

Thank you for looking at my question,

I have three tables SO_Detail, PO_Detail  and SO_vs_PO

SO_Detail has four columns of sales order data: SO_Date, SO_Number, SO_ItemNumber, SO_Qty

PO_Detail has four columns of purchase order data: PO_Date, PO_Number, PO_ItemNumber, PO_Qty

SO_vs_PO has two columns linking Sales Order and Purchase orders: SO_Number. PO_Number

I need to link the three tables in a query so that I can find which Sales Orders do not yet have Purchase Orders raised against them.
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
You might like this:
SELECT  SO_Details.*
FROM    SO_Details
LEFT JOIN SO_vs_PO ON SO_vs_PO.SO_Number = SO_Details.SO_Number
WHERE   SO_vs_PO.SO_Number IS NULL

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsackerContract ERP Admin/ConsultantCommented:
You can, of course, alias your tables and save some keystrokes, but I expanded it above simply for readability. But if you prefer an alias, it could look something like this:
SELECT  s.*
FROM    SO_Details s
LEFT JOIN (SELECT DISTINCT SO_Number FROM SO_vs_PO) v
       ON v.SO_Number = s.SO_Number
WHERE   v.SO_Number IS NULL

Open in new window

The idea is that anything not in the SO_vs_PO table is what you're interested in, since that would indicate no relationship exists for the Sales Order and any Purchase Order(s).

I also threw in a little variation in the LEFT JOIN, a sub-select that grabs only distinct SO_Number values, assuming you might have multiple PO's per SO. If you don't, either query would work just fine. In fact, since you only care about NULL values, the first query (in my first post) should work just as fast (if not faster) than the second one here.
Gary CroxfordOperations Support AnalystAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.