Join the table with itself.
SELECT t1.shopnumber, t1.item from table t1, table t2 where t1.shopnumber =
1 and t1.shopnumber = t2.shopnumber and t1.item != t2.item
Something like that?? Play around with it...
----- Original Message -----
From: "scott" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, October 30, 2002 3:43 PM
Subject: [PHP] mysql question
> Very slightly OT but great minds here
> I have a table with two columns and I need to get all the items that are
> not in the shop number I select, with the exception of items that are in
> the shop number I select.
> Shopnumber item
> 1 orange
> 1 banana
> 1 apple
> 1 pear
> 2 grape
> 2 coca cola
> 2 pepsi
> 3 orange
> 4 orange
> 4 pepsi
> 4 7 up
> 4 sunny delite
> I need to be able to work out all the items that are in the other shops.
> For example if a customer picks orange from shopnumber 1 I need to get a
> result that has all items in all shops except shop1
> The problem is my query still picks items that are in shop1 if they are
> in another shop as well, which is not what I need
> My current query is select * from table where shopnumber!=$shoprvar.
> As an example if I use select * from table where shopnumber!=1 I would
> get grape, coca-cola,pepsi (x2),7 up, sunny delite and orange (x2). I
> don't want orange because it is in shop1!!!!!
> Help and BIG THANKS for the ANSWER?
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php