Date: Fri, 1 Sep 2006 14:12:59 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Walter Cruz <walter.php@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: help with pagila
In-Reply-To: <32cabba0609010631t233d13f9o38edba0483a2446c@mail.gmail.com>
Message-ID: <20060901140554.N12215@megazone.bigpanda.com>
References: <32cabba0609010631t233d13f9o38edba0483a2446c@mail.gmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Fri, 1 Sep 2006, Walter Cruz wrote:

> Hi all. I'm with a little doubt.
>
> I'm testing the pagila (the postgres port of mysql sakila sample).
>
> Well, I was trying to translate the query:
>
> select
>      film.film_id AS FID,
>      film.title AS title,
>      film.description AS description,
>      category.name AS category,
>      film.rental_rate AS price,
>      film.length AS length,
>      film.rating AS rating,
>      group_concat(concat(actor.first_name,_utf8' ',actor.last_name)
> separator ',') AS actors
> from
>      category
>      inner join film on(category.category_id = film.category_id)
>      inner join film_actor on(film.film_id = film_actor.film_id)
>      inner join actor on(film_actor.actor_id = actor.actor_id)
> group by
>     film.film_id;

Assuming that film_id is the primary key on film and category_id is the
primary key on category, I think you'd be allowed to have the other column
references in SQL03 (and 99?) but not in SQL92 (which is the version that
PostgreSQL currently implements).

IIRC, the later specs allow you to not mention columns in group by that
are functionally dependant on other columns that are mentioned.