single record in the timelog table. Or did you mistype and all the
'timelogID's in the subquery should be replaced with 'userID's?
You make a valid point. Not sure the jump to "that's why MySQL has that
feature" is really reasonable, but it's damn near irrelevant anyway.
Cheers,
barneyb
> -----Original Message-----
> From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 08, 2004 3:59 PM
> To: CF-Talk
> Subject: Re: Query of a query inconsistancies
>
> Barney Boisvert wrote:
> >> If only one of them is unique, you need to normalize your data :)
> >
> > I need to pull out a list of users and the number of hours
> they've worked in
> > the past year:
> >
> > SELECT user.userID, user.firstname, user.lastname,
> > SUM(timelog.endTime - timelog.startTime) AS timeWorked
> > FROM user
> > INNER JOIN timelog ON user.userID = timelog.timelogID
> > GROUP BY user.userID [ , user.firstname, user.lastname ]
> > ORDER BY user.lastname, user.firstname, user.userID
> >
> > Why bother having the full select list (minus the aggregate
> column) in the
> > GROUP BY clause, when simply 'user.userID' will do the job?
>
> As long as your data is normalized, standard SQL doesn't require
> you to have anything more in the GROUP BY clause as MySQL does as
> long as you are smart about your SQL:
>
> SELECT u.userID, u.firstname, u.lastname, t.timeWorked
> FROM user u INNER JOIN (
> SELECT timelogID, SUM(endTime - startTime) AS timeWorked
> FROM timelog
> GROUP BY timelogID
> ) t ON u.userID = t.timelogID
> ORDER BY user.lastname, user.firstname, user.userID
>
>
> Naturally this requires you to have a DBMS that supports
> subqueries and this is a query one would typically implement
> using a view if it occured frequently. I believe the (then) lack
> of support for subqueries and the current lack of support for
> views in MySQL is a better explanation for the presence of this
> 'feature' as this being an omission in the SQL standard.
>
> Jochem
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
