Re: sql query wanted

Basil,

see below:

> create table foobar (id varchar(10), sku varchar(10));
>
> insert into foobar values ('1', 'a');
> insert into foobar values ('2', 'b');
> insert into foobar values ('3', 'a');
> insert into foobar values ('3', 'b');
> insert into foobar values ('4', 'c');
> insert into foobar values ('5', 'a');
> insert into foobar values ('5', 'c');
> insert into foobar values ('6', 'a');
> insert into foobar values ('6', 'b');
> insert into foobar values ('7', 'a');
> insert into foobar values ('7', 'b');
> insert into foobar values ('7', 'c');
>
> Now I have to query based on sku: I need all id's where there are row's
> for that id with exactly the sku's provided.
>
> Example:
> if I have a it should return 1
> if I have b it should return 2
> if I have c it should return 4
> if I have a and b it should return 3 and 6
> if I have a and c it should return 5
> if I have a, b and c it should return 7

The intersect operator would be a start but it is not quite enough:

SELECT id FROM foobar WHERE sku = 'a'
INTERSECT
SELECT id FROM foobar WHERE sku = 'b'

would give me 3,6,7 but I only want to get 3,6

additionally as I don't know all possible values of sku I can't create a subquery to eliminate other combinations.

what's more the intersect query on my 12 rows takes a couple of seconds ...

thanks
christian Received on Fri Feb 22 2002 - 11:03:00 CST