How to query two tables based on whether or not record exists in a third?

Question

I have three tables, the first two fairly standard:

1) PRODUCTS table:

• pid
• pname, etc

2) CART table:

• cart_id
• cart_pid
• cart_orderid etc

The third is designed to let people save products they buy and keep notes on them.

3) MYPRODUCTS table:

• myprod_id
• myprod_pid

PRODUCTS.prod_id = CART.cart_prodid = MYPRODUCTS.myprod_pid

When a user orders, they are presented with a list of products on their order, and can optionally add that product to myproducts. I am getting the info necessary for them to do this, with a query something like this for each order:

SELECT cart.pid, products.pname, products.pid
FROM products, cart
WHERE products.pid = cart_prodid 
AND cart_orderid=orderid

This is fine the first time they order.

However, if they subsequently reorder a product which they have already added to myproducts, then it should NOT be possible for them to add it to myproducts again - basically instead of 'Add to MyProducts' they need to see 'View in MyProducts'.

I am thinking I can separate the products using two queries:

1. Products never added to MyProducts

   By somehow identifying whether the user has the product in MyProducts already, and if so excluding it from the query above.

2. Products already in MyProducts

   By reversing the process above.

I need some pointers on how to do this though.

Answer 1

Products they've ordered before:

SELECT cart.pid, products.pname, products.pid 
from products p
inner join cart c on p.prodid = c.cart_prodid
WHERE cart_orderid=orderid

Products they've never ordered before

SELECT products.pname, products.pid 
from products p
left join myproducts mp ON p.prodid = mp.prodid
WHERE mp.prodid IS NULL

The above query may read more naturally as a NOT IN statement, which is to follow. I've prioritized the above because it is most likely a faster operation.

SELECT products.pname, products.pid 
from products p
WHERE p.prodid NOT IN 
(
  SELECT prodid
  FROM myproducts
)

Answer 2

Don't you need a User ID in your myproducts table?

This is not going to answer your specific problem, but have you considered a change in the user interface? Perhaps the user shouldn't control their list of purchased products. This seems like you are unnecessarily complicating your interface. You could automatically add every product they ordered to "My Products".

Also, I think you should keep track of how many products the user ordered of the same product ID. Put a "count" field in the myproducts table to keep track of this. You don't have to actually use this field, but you may eventually regret not adding this. Or even better - add a new row for every added product so you can keep track of the date/time as well.