Re: SQL for

If your rows are unique you can do:

select bar from foobar f1 where 3 = (select count(*) from foobar f2 where f1.bar = f2.bar);

Regards,

Wim Valgaeren

<danielsomerfield_at_my-deja.com> wrote in message news:812qqd$3i9$1_at_nnrp1.deja.com...
> Hello, I was wondering if someone could help me with a query. I have
> found a long, rather convoluted inefficient way to do it and I am
> wondering if there is a better one. If I have a table like so:
> foobar
> foo | bar
> |
> ---------
> 1 | a
> 1 | b
> 1 | c
> 2 | a
> 3 | a
> 3 | b
>
> I wish to get all the "bar"s which have "foo"s of 1, 2 and 3. So the
> answer above is "a". The query I came up with is:
>
> SELECT bar FROM foobar WHERE foo = 1
> INTERSECT
> SELECT bar FROM foobar WHERE foo = 2
> INTERSECT
> SELECT bar FROM foobar WHERE foo = 3;
>
> Ugly and cumbersome. What do you think? Is there something a bit more
> elegant?
>
> Dan
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Nov 19 1999 - 03:45:33 CST