SELECT WHERE problem
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 rowsbut 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]
Re: SELECT WHERE problem
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 rowsbut 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]
Re: SELECT WHERE problem
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 rowsbut 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]
Re: SELECT WHERE problem
Because not doing so violates the SQL standard. Allowing you to included non aggregated columns in the SELECT list is a non standard MySQL extension to the SQL language. You will get an error in other products, such as oracle, where you will get a xxx is not a group by expression error. Information on this feature is here: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html On Mon, 9 Aug 2004 17:22:17 -0600, Ren Fournier [EMAIL PROTECTED] wrote: 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 rowsbut 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
