Re: simple? join

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