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.