ShootingBlanks Posted December 10, 2007 Share Posted December 10, 2007 Hello. I have data in a "projects" table with "proj_id" (primary key) and "proj_name" columns. I also have a lookup table called "update_progress" with three columns: * proj_id (which relates to the proj_id primary key in the "projects" table) * updated (a TIMESTAMP that auto-updates the current timestamp on update) * progress (a text field) Now, I need to display each project title (from the "projects" table) one time, and then I need to display only the most RECENT progress that relates to it (from the new "update_progress" table) after that. This is where I'm having problems... I have it so that it's displaying EVERY update (not just the most recent one). So if there's three updates, the project will list three times - once for each update... Here's the code that's doing that - please let me know how to fix it. Thanks!: SELECT projects.proj_id, projects.proj_name, CONCAT(DATE_FORMAT(update_progress.updated, '%Y-%m-%d'),': ',update_progress.progress) AS progress FROM projects, update_progress WHERE projects.proj_id = update_progress.proj_id Quote Link to comment https://forums.phpfreaks.com/topic/81045-getting-what-i-need-out-of-a-query/ Share on other sites More sharing options...
fenway Posted December 10, 2007 Share Posted December 10, 2007 You need to find the most recent update first (group by and max) then join this to the proj tables. Quote Link to comment https://forums.phpfreaks.com/topic/81045-getting-what-i-need-out-of-a-query/#findComment-411241 Share on other sites More sharing options...
ShootingBlanks Posted December 10, 2007 Author Share Posted December 10, 2007 You need to find the most recent update first (group by and max) then join this to the proj tables. I'm kind of a novice at this. Can you be more specific at all? Thanks! Quote Link to comment https://forums.phpfreaks.com/topic/81045-getting-what-i-need-out-of-a-query/#findComment-411242 Share on other sites More sharing options...
fenway Posted December 10, 2007 Share Posted December 10, 2007 Run a query with MAX(updated) on the progress table and group by proj_id. Then use this derived table and join on proj_id, and show the desired columns. Quote Link to comment https://forums.phpfreaks.com/topic/81045-getting-what-i-need-out-of-a-query/#findComment-411293 Share on other sites More sharing options...
ShootingBlanks Posted December 10, 2007 Author Share Posted December 10, 2007 Run a query with MAX(updated) on the progress table and group by proj_id. Then use this derived table and join on proj_id, and show the desired columns. How do you run a query based on another query's results. I guess that's my real question. Sorry for my newbie questions! :-\ Quote Link to comment https://forums.phpfreaks.com/topic/81045-getting-what-i-need-out-of-a-query/#findComment-411303 Share on other sites More sharing options...
fenway Posted December 11, 2007 Share Posted December 11, 2007 Untested: SELECT p.proj_id, p.proj_name, CONCAT(DATE_FORMAT(up.updated, '%Y-%m-%d'),': ',up.progress) AS progress INNER JOIN update_progress AS up ON ( up.proj_id = p.proj_id ) INNER JOIN ( SELECT proj_id, MAX(updated) AS maxUpdated FROM update_progress GROUP BY proj_id ) AS sub ON ( sub.proj_id = up.proj_id AND sub.maxUpdated = up.updated ) [code] [/code] Quote Link to comment https://forums.phpfreaks.com/topic/81045-getting-what-i-need-out-of-a-query/#findComment-411943 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.