Date: Fri, 9 Oct 2009 16:06:56 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: =?utf-8?Q?Dag-Erling_Sm=C3=B8rgrav?= <des@des.no>
Cc: pgsql-sql@postgresql.org
Subject: Re: Existential quantifier
In-Reply-To: <86bpkgqiv8.fsf@ds4.des.no>
Message-ID: <20091009160318.J80119@megazone.bigpanda.com>
References: <86bpkgqiv8.fsf@ds4.des.no>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=X-UNKNOWN
Content-Transfer-Encoding: QUOTED-PRINTABLE

On Sat, 10 Oct 2009, [utf-8] Dag-Erling Sm=C3=B8rgrav wrote:

> Consider the attached schema (filmstars.sql), which is a poorly designed
> database of films and actors.  The following query gives me a list of
> films in which either Charlie or Martin Sheen starred:
>
> select fs.film.title, fs.film.year
>   from fs.film left join fs.star on fs.film.id =3D fs.star.film
>   where fs.star.last =3D 'Sheen'
>   group by fs.film.title, fs.film.year;
>
> Is there a way to do this without the "group by" clause?

Not at all tested as I don't have access to my db right now, but I think
something like one of these would work:

select fs.film.title, fs.film.year
 from fs.film
 where exists(select 1 from fs.star where fs.film.id =3D fs.star.film
  and fs.star.last =3D 'Sheen');

select fs.film.title, fs.film.year
 from fs.film
 where fs.film.id in (select fs.star.film where fs.star.last =3D 'Sheen');