Message-Id: <3.0.6.32.20020108140319.00864e00@pop6.sympatico.ca>
Date: Tue, 08 Jan 2002 14:03:19 -0500
To: <pgsql-sql@postgresql.org>
From: Frank Bax <fbax@sympatico.ca>
Subject: Re: simple? join
In-Reply-To: <Pine.LNX.4.30.0201071746120.8360-100000@peter.localdomain>
References: <3.0.6.32.20020107170828.0099e8b0@pop6.sympatico.ca>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 05:52 PM 1/7/02 -0500, Peter Eisentraut wrote:
>Frank Bax writes:
>
>> At 12:22 AM 1/6/02 -0500, Peter Eisentraut wrote:
>> >Frank Bax writes:
>> >> EMPLOYEE table contains: emp, first, last (emp is unique key)
>> >> TIMESHEET table contains: emp, timestamp, hours
>> >> I want to report employee name and total hours.
>> >> SELECT first, last, sum(ts.hours)
>> >>   FROM timesheet ts, employee emp
>> >>  WHERE ts.emp = emp.emp
>> >>  GROUP by emp.emp, first, last
>> >>  ORDER BY last, first;
>> >>
>> >> It seems silly to specify extraneous "group by" fields.
>> >
>> >There's nothing "extraneous" there.  Both first and last could be
>> >duplicated, so you need to group by each one.
>>
>> But first and last can't be duplicated if emp is defined as unique.
>
>Strictly speaking, you're right.  However, by the time you get to GROUP BY
>the tables have been joined so the notion of a unique constraint has been
>lost.  Maybe it shouldn't, but as it stands, there's nothing you can do
>better here.
>
>> If I
>> am also selecting a dozen or so other fields from "employee" table, must I
>> also include them all in the GROUP BY clause, even though I know "emp"
>> identifies a unique row in this table?
>
>Yes you do.  There's the possibility to write it differently like so:
>
>SELECT *
>FROM
>    (SELECT emp, sum(ts.hours)
>     FROM timesheet ts, employee emp
>     WHERE ts.emp = emp.emp
>     GROUP by emp.emp) AS a
>    INNER JOIN
>    (SELECT emp, first, last, more, things, here FROM employee) AS b
>    ON (a.emp = b.emp)
>...
>
>This could be useful if the second query in the inner join involves more
>than one table, but on the whole this can get pretty messy.

Thanks for the sample!! A few more changes, and it's quite readable:
SELECT * FROM
    (SELECT emp, sum(hours) FROM timesheet GROUP by emp) AS ts
  INNER JOIN
    (SELECT emp, first, last FROM employee) AS emp
  ON (ts.emp = emp.emp)

As an added bonus, this runs almost twice as fast as either of the
originals (both original versions used same plan)!

Frank