From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: simple? join
Date: Tue, 08 Jan 2002 14:03:19 -0500
Message-ID: <3.0.6.32.20020108140319.00864e00@pop6.sympatico.ca> (raw)
In-Reply-To: <Pine.LNX.4.30.0201071746120.8360-100000@peter.localdomain>
References: <3.0.6.32.20020107170828.0099e8b0@pop6.sympatico.ca>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fbax@sympatico.ca
Subject: Re: simple? join
In-Reply-To: <3.0.6.32.20020108140319.00864e00@pop6.sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox