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