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 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. 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! 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. 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! :-\ 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] 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
Archived
This topic is now archived and is closed to further replies.