Morten wrote:
>
>
> Hi,
>
> Given the table keyval(key int(11), val int(11)), I would like to be
> able to retrieve the keys for which a row exist for given X values.
>
> Example:
>
> key value
> 1 8
> 1 9
> 1 10
> 2 8
> 3 8
> 3 10
> 4 8
> 4 9
> 4 10
> 4 11
>
> Given values 8, 9, 10 the query should thus return 1 and 4. The possible
> number of values is variable.
>
> Can this be expressed somewhat more elegantly than multiple EXISTS
> subqueries?
>
> SELECT DISTINCT key FROM keyval outer
> WHERE EXISTS (SELECT * FROM keyval inner
> WHERE outer.key = inner.key
> AND inner.val = 8)
> AND EXISTS (SELECT * FROM keyval inner
> WHERE outer.key = inner.key
> AND inner.val = 9)
> ...
>
> Br,
>
> Morten
>
>
>
How about:
SELECT DISTINCT `key`, COUNT(`key`) AS c
FROM `table` WHERE `value` in (8,9,10)
HAVING c=3;
--
Gerald L. Clark
Supplier Systems Corporation
