select with is not()

serichens

I need help with my select statement. I'm essentially trying to get the record that does not match the others.

in table cart,
cookieId | prodId
1 | 123
1 | 314

in table relatedProds
relprodId | crossSellId
123 | 347
123 | 288
123 | 314
314 | 250

So, this person has products 123 and 314 in their cart. Product 123 relates to products 347, 288, and 314.

Based on the product in the cart, I want to select any related products to display in an ad.

However, if the person already has product 314 in their cart, do not display product 314 in the ad routine.

So, in this case the right answer would be 347, 288, 250.

I've tried various combinations, but haven't hit the right one. Any thoughts?

serichens

the latest:

SELECT cart.cartId, cart.cookieId, cart.prodId, relatedProds.relProdId, relatedProds.crossSellId
FROM cart, relatedProds
WHERE
cart.cookieId = '1' AND
relatedProds.relprodId = cart.prodId AND
cart.prodId NOT IN( relatedProds.crossSellId )

this yields all the related products: 288, 314, 347, 250

serichens

ok, this query gets the right answer:

SELECT cart.cartId, cart.cookieId, cart.prodId,
relatedProds.relProdId, relatedProds.crossSellId
FROM cart, relatedProds
WHERE cart.cookieId = '930b717a21c8040b5d171d4ebbc69685'
AND relatedProds.relprodId = cart.prodId
AND '314' NOT IN(relatedProds.crossSellId)

but, this one:
SELECT cart.cartId, cart.cookieId, cart.prodId,
relatedProds.relProdId, relatedProds.crossSellId
FROM cart, relatedProds
WHERE cart.cookieId = '930b717a21c8040b5d171d4ebbc69685'
AND relatedProds.relprodId = cart.prodId
AND cart.prodId NOT IN(relatedPRods.crossSellId)
does not.

How can I get cart.prodId to evaluate to 314 in the same query? I don't want to do multiple queries to find the value of cart.prodId

Roger_Ramjet

OK nested queries: http://dev.mysql.com/doc/mysql/en/EXISTS_and_NOT_EXISTS_subqueries.html

SELECT DISTINCT crossSellId FROM relatedprods, cart WHERE relatedprods.relprodId=cart.prodId

will return 1 instance of all related prod ids for all items in your cart. To elliminate the ids already in the cart, we use

SELECT DISTINCT crossSellId AS Id FROM relatedprods, cart WHERE relatedprods.relprodId=cart.prodId WHERE NOT EXISTS (SELECT prodId AS Id FROM cart)

Note that the column names have to match so we need to use the AS operator. Better to rename the columns in your tables; they should be named the same anyway since they refer to the same thing.

serichens

that makes sense & I read through nested queries.

But I keep getting a syntax error. Should there be 2 WHERE's in the statement?

I can get each query to execute individually, but not having any luck with the syntax of a nested query.

serichens

// correct syntax, no results.

SELECT DISTINCT crossSellId AS Id FROM relatedProds, cart WHERE relatedProds.relprodId=cart.prodId AND cart.cookieId = '1' NOT IN('SELECT prodId AS Id FROM cart')

same with IN('SELECT....

yelvington

Note that subqueries do not work in MySQL versions earlier than 4.1.