Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 user_run_times and run_favourites table.

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

The column 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 run_favourites)

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! :)

share|improve this question
1  
Try COUNT(DISTINCT runFavouriteID), I'm not sure if MySQL supports that though. –  Simon Righarts Jan 11 '12 at 0:08

1 Answer 1

up vote 1 down vote accepted

This is my best shot without seeing any table structures

SELECT
    user_runs.userID,
    user_runs.runID,
    `createdBy`,
    `username`,
    DATE_FORMAT(`dateCreated`,'%d-%m-%Y') `dateCreated`, 
    COUNT(DISTINCT runFavouriteID) AS favourite,
    DATE_FORMAT(MAX(`runDate`),'%d-%m-%Y') AS `lastRan`,
    MIN(`runTime`) AS `fastestTime`
FROM
    (SELECT * FROM `user_runs` WHERE userID=4) 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
GROUP BY
    user_runs.runID,`createdBy`,`username`,`dateCreated`;

You will need these indexes

ALTER TABLE user_runs ADD INDEX userID_runID_ndx (userID,runID);
ALTER TABLE runs      ADD INDEX runID_createdBy_ndx (runID,createdBy);
ALTER TABLE runs      ADD INDEX createdBy_runID_ndx (createdBy,runID);
share|improve this answer
    
Thanks for your answer, could you please let me know how I can show the table structure in a format like: dba.stackexchange.com/questions/10472/join-three-tables ? Otherwise would the CREATE TABLE queries be okay? –  Elliott Jan 11 '12 at 9:16
    
Just tested that and it seemed to have worked, I think the DISTINCT fixed the main problem. I didn't have to add those indexes as I already had an index for each. Thanks! –  Elliott Jan 11 '12 at 10:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.