List:General Discussion« Previous MessageNext Message »
From:Morten Date:August 4 2006 6:46pm
Subject: Tricky query
View as plain text  

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


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