How to query two tables based on whether or not record exists in a third?
- by Katherine
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:
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.
Products already in MyProducts
By reversing the process above.
I need some pointers on how to do this though.
