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