Products not on master file

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I tring to build a query that will return all product
ID's from details table that are not in the master
product table. Can someone tell me how this should be
done? In Oracle it would simply look like this:

SELECT DISTINCT TBL_Details.ProdCd
FROM TBL_Details
WHERE TBL_Details.ProdCd Not In (SELECT
TBL_ProductMst.ProdCd FROM TBL_ProductMst);
 
I tring to build a query that will return all product
ID's from details table that are not in the master
product table. Can someone tell me how this should be
done? In Oracle it would simply look like this:

SELECT DISTINCT TBL_Details.ProdCd
FROM TBL_Details
WHERE TBL_Details.ProdCd Not In (SELECT
TBL_ProductMst.ProdCd FROM TBL_ProductMst);
Click to expand...

That should work in Access as well, albeit slowly - the JET query
engine doesn't optimize NOT IN() queries very well.

A "Frustrated Outer Join" is more efficient:

SELECT DISTINCT TBL_Details.ProdCd
FROM tblDetails LEFT JOIN tblProdMst
ON tblDetails.ProdCd = tblProdMst.ProdCd
WHERE tblProdMst.ProdCd IS NULL;
 
Back
Top