SQL Query

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!

"Jay Calvert" 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?
>
>
>Thanks!

select * from tablename where itemid not in (1,4)

--
http://www.dbForumz.com/  This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/PHP-SQL-Que...pict141066.html
Visit Topic URL to contact author (reg. req'd).  Report abuse: http://www.dbForumz
.com/eform.php?p=471680

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. I
t
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

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 MySQ
L
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" wrote:
> On 18 Aug 2004 16:46:20 -0400, steve
> <UseLinkToEmail@dbForumz.com> wrote:
> 
>
>  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, you lost me on that one.

--
http://www.dbForumz.com/  This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/PHP-SQL-Que...pict141066.html
Visit Topic URL to contact author (reg. req'd).  Report abuse: http://www.dbForumz
.com/eform.php?p=471758

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