simplest query to move all saved not items not found in cart

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

simplest query to move all saved not items not found in cart

Post 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

	)

User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have you looked at the JOIN provided in one of the Useful Posts thread links?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Useful Posts is a thread in PHP - Code. Currently, it's linked from my signature too.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I don't see why it wouldn't work. It's the same principle.
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post 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
Post Reply