Getting what I need out of a query...

[ShootingBlanks]

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

[fenway]

You need to find the most recent update first (group by and max) then join this to the proj tables.

[ShootingBlanks]

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!

 

[fenway]

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.

[ShootingBlanks]

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

 

 

[fenway]

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]