Thanks, the LEFT JOIN worked.
I do have a question though, why is it considered best practice to list all non-aggregated columns ( I assume you mean columns from trucks.*) in the GROUP BY statement? I ask because I am interested in fast, secure, standards-compliant code, I'm just not always sure what that is. :-)
...René
--- René Fournier, www.renefournier.com
On Aug 9, 2004, at 2:14 PM, [EMAIL PROTECTED] wrote:
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
