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
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
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
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.
