List:General Discussion« Previous MessageNext Message »
From:Gerald L. Clark Date:August 4 2006 7:34pm
Subject:Re: Tricky query
View as plain text  
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
> 
> 
> 
I forgot the GROUP BY

How about:
SELECT DISTINCT `key`, COUNT(`key`) AS c
FROM `table` WHERE `value` in (8,9,10)
GROUP BY `key`
HAVING c=3;

-- 
Gerald L. Clark
Supplier Systems Corporation

Thread
Tricky queryMorten4 Aug
  • Re: Tricky queryPeter Brawley4 Aug
    • Re: Tricky queryMorten4 Aug
  • Re: Tricky queryGerald L. Clark4 Aug
    • Re: Tricky queryMorten4 Aug
  • Re: Tricky queryGerald L. Clark4 Aug