Home > Archive > PHP SQL > August 2004 > SQL Query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Jay Calvert 2004-08-18, 8:56 pm |
| BasketContents
id basketID itemID
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 3 2
7 3 3
How do I find those baskets that don't have itemID 's 1 and 4?
Thanks!
| |
|
|
| Andy Hassall 2004-08-18, 8:56 pm |
| On 18 Aug 2004 16:46:20 -0400, steve <UseLinkToEmail@dbForumz.com> wrote:
>"Jay Calvert" wrote:
>
>select * from tablename where itemid not in (1,4)
No, that selects the items that the basket does have, that aren't 1 and 4. It
doesn't select the baskets that have neither 1 nor 4.
--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
| |
| Andy Hassall 2004-08-18, 8:56 pm |
| On Wed, 18 Aug 2004 20:01:46 GMT, "Jay Calvert" <jcalvert@nospam.com> wrote:
>BasketContents
>id basketID itemID
>1 1 1
>2 1 2
>3 1 3
>4 2 1
>5 2 2
>6 3 2
>7 3 3
>
>
>How do I find those baskets that don't have itemID 's 1 and 4?
Using which database? The standard and intuitive approach won't work on MySQL
as it doesn't support subqueries - i.e. something like:
select distinct basketID
from BasketContents
where basketID not in (select basketID
from BasketContents
where itemID in (1,4))
An alternative that works in MySQL could be:
select b1.basketID
from BasketContents b1
left outer join BasketContents b2
on (b1.basketID = b2.basketID
and b2.itemID in (1,4))
group by b1.basketID
having count(b2.itemID) = 0;
+----------+
| basketID |
+----------+
| 3 |
+----------+
1 row in set (0.01 sec)
Or:
select b1.basketID
from BasketContents b1
left outer join BasketContents b2
on (b1.basketID = b2.basketID and b2.itemID in (1,4))
group by b1.basketID
having count(distinct b2.itemID) < 2;
+----------+
| basketID |
+----------+
| 1 |
| 2 |
| 3 |
+----------+
3 rows in set (0.01 sec)
Depends what you want: baskets that have neither of (1,4), or baskets that
don't have both of (1,4).
--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
| |
|
|
| Andy Hassall 2004-08-18, 8:56 pm |
| On 18 Aug 2004 17:39:09 -0400, steve <UseLinkToEmail@dbForumz.com> wrote:
>"Andy Hassall" wrote:
>
>Andy, you lost me on that one.
mysql> select * from BasketContents;
+------+----------+--------+
| id | basketID | itemID |
+------+----------+--------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
+------+----------+--------+
4 rows in set (0.00 sec)
mysql> select * from BasketContents where itemID not in (1,4);
+------+----------+--------+
| id | basketID | itemID |
+------+----------+--------+
| 2 | 1 | 2 |
| 3 | 1 | 3 |
+------+----------+--------+
2 rows in set (0.00 sec)
basketID=1 does have both itemID=1 and itemID=4, but your query returns
basketID=1 anyway, as it has other items as well.
--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
|
|
|
|
|