Re: simple? join

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.

--
Peter Eisentraut peter_e(at)gmx(dot)net