PDA

View Full Version : Getting what I need out of a query...



ShootingBlanks
12-10-2007, 06:37 PM
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

Twey
12-10-2007, 08:08 PM
ORDER BY update_progress.updated LIMIT 1

ShootingBlanks
12-10-2007, 08:16 PM
ORDER BY update_progress.updated LIMIT 1

That just gives me one result total, though. I need one result for each project listed.