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
