Page 1 of 1
simplest query to move all saved not items not found in cart
Posted: Fri Aug 24, 2007 10:14 am
by raghavan20
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.
Code: Select all
SELECT
sil.productId
FROM
SavedItemsList sil
WHERE
userId = $userId
AND
productId NOT IN
(
SELECT
productId
FROM
ShoppingCart
WHERE
userId = $userId
)
Posted: Fri Aug 24, 2007 10:37 am
by feyd
Have you looked at the JOIN provided in one of the Useful Posts thread links?
Posted: Fri Aug 24, 2007 10:45 am
by raghavan20
feyd wrote:Have you looked at the JOIN provided in one of the Useful Posts thread links?
i could not find that thread under 'useful resources' of database forum.
Posted: Fri Aug 24, 2007 10:53 am
by feyd
Useful Posts is a thread in PHP - Code. Currently, it's linked from my signature too.
Posted: Fri Aug 24, 2007 11:05 am
by raghavan20
feyd wrote:Have you looked at the JOIN provided in one of the Useful Posts thread links?
hello feyd, your solution described here
here would not work for this situation because of these two influencing parameters, user id and product id.
Posted: Fri Aug 24, 2007 11:18 am
by feyd
I don't see why it wouldn't work. It's the same principle.
Posted: Fri Aug 24, 2007 4:33 pm
by josa
Code: Select all
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.
/josa
Posted: Sun Aug 26, 2007 10:45 am
by raghavan20
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.
Code: Select all
shopping cart
product id user id
077 1
078 2
079 3
saved item
product id user id
077 1
078 1
079 1
089 2
Posted: Sun Aug 26, 2007 1:06 pm
by josa
The join will take care of that I think. If you run the query like this (including all columns and without the where clause) you will see what i mean:
Code: Select all
SELECT sil. * , sc. *
FROM SavedItemsList sil
LEFT JOIN ShoppingCart sc ON sil.productId = sc.productId AND sil.userId = sc.userId
/josa