i have two tables, saveditemslist and shoppingcart. they both have two fields in common userid and productid. a user may want to move all saved items to shopping cart. but we will only move the ones that does not already exist in shopping cart. what is the simplest way to find the products that exist in saved items but not in shopping cart in one query? i tried some joins, it did not work properly.
EDIT:
I am thinking of the below but I think this won't work faster in mysql.
SELECT sil.productId FROM SavedItemsList sil
LEFT JOIN ShoppingCart sc
ON sil.productId = sc.productId AND sil.userId = sc.userId
WHERE sc.productId IS NULL AND sil.userId = $userId
This is one way to do it, I think... But I should warn you that my posts has not been very well thought trough lately, so you might want to get a second opinion.
if you join on both the fields user id and product id, then i think where would be a row matching where product id is null and user id is equal. please try it with these sample values.