I'm working on joining multiple tables together to extract some data related to user "runs", this includes basics like runID, username etc but also joins to the run times and favourites.
I have the query below which uses a
LEFT JOIN for the
The query should show all runs for the user, the latest date ran, if it's a favourite, and the quickest run time - All of which is displayed, but if more than one time is logged the favourites count goes above one (should only ever be one or zero).
SELECT user_runs.userID, user_runs.runID, `createdBy`, `username`, DATE_FORMAT(`dateCreated`,'%d-%m-%Y') `dateCreated`, COUNT(runFavouriteID) AS favourite, DATE_FORMAT(MAX(`runDate`),'%d-%m-%Y') AS `lastRan`, MIN(`runTime`) AS `fastestTime` FROM `user_runs` INNER JOIN `runs` ON user_runs.runID = runs.runID INNER JOIN `users` ON users.userID = runs.createdBy LEFT OUTER JOIN `user_run_times` ON user_run_times.runID = user_runs.runID LEFT OUTER JOIN `run_favourites` ON run_favourites.runID = user_runs.runID WHERE user_runs.userID = 4 GROUP BY runID, user_run_times.runID;
I'm looking for some suggestions on how to improve this query? The current output is:
UserID runID createdBy username dateCreated favourite lastRan fastestTime 4 3 3 bob 11-12-2011 3 27-01-2012 36920030
favourite should only display one (as there is one row in
run_favourites) but it shows three (two for
user_run_times and one for
Hopefully I haven't made this too confusing, if somebody could suggest how I can output the table structure in a "text" format like most on here then I will also post those.
Any advice appreciated, thanks! :)