List:General Discussion« Previous MessageNext Message »
From:Morten Date:August 4 2006 7:28pm
Subject:Re: Tricky query
View as plain text  
Peter Brawley wrote:
>  >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);

The tricky part is that there must be a record for EACH of the values 
(8, 9, 10) and not just any one value (as IN requires). Your proposal 
will return 1,2,3,4 and not just 1,4.

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