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

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
0
Patrick O'Dea
Asked:
Patrick O'Dea
2 Solutions
 
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
 
santoshmotwaniCommented:
select * from tblproduct , tblsupplier
where
supplierid = productid
and
supplierid not in ( select supplierid from tblprice )
and
productid not in ( select productid from tblprice)
0
 
Rey Obrero (Capricorn1)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
 
Patrick O'DeaAuthor Commented:
Thanks capricorn1 - Perfect!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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