From: Peter Brawley
Date: August 4 2006 7:15pm
Subject: Re: Tricky query
List-Archive: http://lists.mysql.com/mysql/200643
Message-Id: <44D39CE1.9020107@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >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