SQL to Find where NOT EXISTS

Hi,

See attached - very simple database.
Table of Suppliers.
Table of products.
Table of prices of products for suppliers.

In theory, there should be a price in the prices table for every supplier/product combination.

I want a query that will show all Supplier/Product combinations that DO NOT exist in the tblPrices.


Database2.mdb
Patrick O'DeaAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this

Select A.[ProductID],A.[SupplierID]
From
(
SELECT tblProduct.ProductID, tblSupplier.SupplierID
FROM tblProduct, tblSupplier
) A
Left Join tblPrice P On
A.[ProductID]=P.[ProductID] And A.[SupplierID]=P.[SupplierID]
Where P.[ProductID] is null And P.[SupplierID]  is null
0
 
SharathData EngineerCommented:
Can you zip the attachment and upload it as I could not able to download the mdb extension files. If not, you can post some sample data with expected result. Otherwise, I hope someone will look into this.
0
 
Patrick O'DeaAuthor Commented:
Zipped as requested
Database2.zip
0
 
santoshmotwaniConnect With a Mentor Commented:
select * from tblproduct , tblsupplier
where
supplierid = productid
and
supplierid not in ( select supplierid from tblprice )
and
productid not in ( select productid from tblprice)
0
 
Patrick O'DeaAuthor Commented:
Thanks capricorn1 - Perfect!
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.