>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)
Would you be looking for ...
SELECT DISTINCT key
FROM keyval k1
JOIN keyval k2 USING (key)
WHERE k1.val IN(8,9,10);
?
PB
-----
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
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
