PHP/MySQL Multiple table join

[dinks]

Hi,
I'm not sure if this is a valid topic as this is a PHP forum and my question relates more to MySQL however i found a thread that seems similar to mine (but not quite what i'm looking for) here (INNER join users, progress ON (projects.id=progress.project_id AND progress.username=users.username) and was hoping i could get some help.

I'm wondering if it's possible to get a results table that looks like the following:

____ ¦project1¦project2¦project3¦
--------------------------------------
user1¦ tot hrs ¦ tot hrs ¦ tot hrs ¦
user2¦ tot hrs ¦ tot hrs ¦ tot hrs ¦
user3¦ tot hrs ¦ tot hrs ¦ tot hrs ¦

There can be unlimited number of users, unlimited number of projects, and i need to have the hours that a user has invested in a project be summed.

From a single table join query of the following tables. I've referenced the important fields in the tabls:

USERS
username PK
first_name
last_name

PROJECTS
id PK
name

PROGRESS
id PK
username
project_id
hours

I came up with the following query which is close but not presented quite the way i need it:

SELECT
projects.name,
users.first_name,
users.last_name,
SUM(progress.hours)
FROM
projects
INNER join users, progress ON (projects.id=progress.project_id AND progress.username=users.username) GROUP BY projects.id, users.username

Any help would be greately appreciated.
Thanks

[mcibor]

WOuld this do?

SELECT users.first_name,
users.last_name,
projects.name,
SUM(progress.hours) as total
WHERE progress.username=users.username
AND progress.project_id=projects.id
GROUP BY progress.username, progress.project_id;

I know this is a bit different, as it will give you:

user1, Project1, 48
user1, Project2, 10
user2, Project1, 40
user2, Project3, 49

The one you mentioned above I have no idea how to do that yet, sorry. However if you succede please write it here, as I'm very interested!

Best regards
Michal Cibor

[dinks]

Thanks for the reply. This is pretty similar to the results i as getting though and i really would like to figure out how to get a table like the one i ask for above.
Any other ideas?