You need to change your INNER JOIN to a LEFT JOIN
SELECT
trucks.id, sum(history.time_sec) as total_seconds
FROM
trucks
LEFT JOIN
history
ON trucks.id = history_truckid
GROUP BY
trucks.id
ORDER BY
total_seconds desc
One other issue --->IMHO, the SQL engine is being too kind when it allows
you to execute a query like "SELECT trucks.* .... GROUP BY ....". In
practically EVERY OTHER SQL-based product you will use, you will be
required to list _all_ non-aggregated columns in your GROUP BY statement
or you will get an error. Listing every column you want to group on is
considered "proper SQL format" and I highly recommend the practice.
If you still want to see everything from your trucks table (like in your
original query) you can do this:
CREATE TEMPORARY TABLE tmpTruckIDs
SELECT
trucks.id, sum(history.time_sec) as total_seconds
FROM
trucks
LEFT JOIN
history
ON trucks.id = history_truckid
GROUP BY
trucks.id
ORDER BY
total_seconds desc;
SELECT trucks*, tmpTruckIDs.total_seconds
FROM trucks
INNER JOIN tmpTruckIDs
ON tmpTruckIDs.id = trucks.id;
DROP TABLE tmpTruckIDs;
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Renà Fournier <[EMAIL PROTECTED]> wrote on 08/09/2004 03:56:58 PM:
> I am having a problem building a SELECT statement that joins two tables
> with a WHERE condition.
>
>
> SELECT
> trucks.*
>
> FROM
> trucks, history
>
> WHERE
> trucks.account_id = '100'
> AND trucks.status = 'Active'
> AND history.truck_id = trucks.id <<<< This is the
> tricky bit
>
> GROUP BY
> trucks.id
>
> ORDER BY
> history.time_sec DESC
>
>
> Simply put (or as simply as I can put it :-) , this SELECT should
> return all trucks in order of their activity (history.time_sec). The
> problem is when a truck is new to the system and does not have a single
> record in the history table (and therefore no value for
> history.time_sec). In that case, the truck is excluded from the
> SELECTed rowsâbut I want it returned, just at the bottom of the list
> (least active).
>
> Any ideas how this can be done?
>
> ...Rene
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
