Help: Rows from one table that are NOT in another.

[JMoritz]

I have three tables: tblProducts,tblOrders,and tblTempCart.

Process:

When a product is chosen, it is added to the tblTempCart table based on the OrderID then the customer is returned to the products page.

Problem:

I want to write a query that gets all the products from the tblProducts table that are NOT listed in the tblTempCart table. I have tried everything but can't seem to find out how to do it. Each product is unique so customers must be prevented from buying the same product twice. After adding a product to their cart and return to shopping, I don't want that same product to be listed on a products page. The relationships are as follows and the three tables are related this way:

tblProducts.ProductsID->
tblTempCart.intTCProductsID
tblTempCart.intTCOrdersID->
tblOrders.OrdersID

I have used query designer for years and has always served my purpose until now. Any help or link to proper resources would be helpful. Thanks.

John Moritz

 

[NullTerminator]

Try this

select * from tblProducts P
left join tblTempCart C
on P.ProductID = C.ProductID
where C.ProductID -- or any C field
Is Null

 

[sunila7]

Hi,

TRy htis query, to list producyts in tblTempCart and not in tblProducts

U can use one of these queries....

Select * from tblProducts P where not exists (select * from tblTempCart T where P.ProductsID=
T.intTCProductsID)
OR

Select * from tblProducts P where P.ProductsID NOT IN (SELECT intTCProductsID from tblTempCart)

the first query should be more efficient than the second one.hope it helps....

Sunil

 

[JMoritz]

This is great, I have already learned several things from these great replies. I am almost there and would appreciate just one more hint. There are several records in the tblTempCart table based on current Orders. Each customer is associated with an OrdersID. How can I disclude Products that are listed in the tblTempCart but are only associated with one Order?

tblProducts
------------
tblProducts.ProductsID

tblOrders
------------
tblOrders.OrdersID

tblTempCart
------------
tblTempCart.TempCartID
tblTempCart.intTCOrdersID
tblTempCart.intTCProductsID

Sorry if I wasn't as clear in my question to begin with. I can see where I wasn't. Thanks.

John Moritz

 

[JMoritz]

Doh!!! I figured it out!! Thanks for all the help! Wouldn't have been able to do it otherwise.

John Moritz