CODEWITHSUNDEEP
mysql
nikoh
nikoh

Reputation: 101

Show users participants to project

To start, I've just spent 12 hours straight trying to figure out how this works and I've learned lots of terms and definitions but I can't get it right. I've researched:

and much more!

What I want to achieve is to show all the projects in a list, next to the project name I want to show the project leader and all the participants for each project if any exists.

So the database structure right now:

DB: PROJECTS
PROJECT_ID  PROJECT_NAME  PROJECT_LEADER
1           MY PROJECT    1
2           UPDATE THIS   1
3           STATUS FIX    2

DB: USERS
USER_ID  USER_FIRSTNAME
1        ADAM
2        BRIAN

DB: PARTICIPANTS
PARTICIPANT_ID  PROJECT_ID  USER_ID
1               1           2

And the SQL QUERY:

SELECT *
FROM PROJECTS P
JOIN users u ON p.project_leader = u.user_id

This is what I get when running the query:

MY PROJECT - ADAM
UPDATE THIS - ADAM
STATUS FIX - BRIAN

What I would like to see is this:

MY PROJECT - ADAM & BRIAN
UPDATE THIS - ADAM
STATUS FIX - BRIAN

Any help would be worth gold! Big thanks in advance.

Upvotes: 0

Views: 129

Answers (1)

Nick
Nick

Reputation: 147236

This query will do what you want. Essentially to get the names of the project leader and the participants, you need to join to the users table twice, first for the project leader's name and then for the participant's names. You also need to use a LEFT JOIN on participants and the second user table in case there aren't any participants in a project.

SELECT P.PROJECT_NAME, U1.USER_FIRSTNAME AS PROJECT_LEADER, GROUP_CONCAT(U2.USER_FIRSTNAME) AS PARTICIPANTS
FROM PROJECTS P
JOIN USERS U1 ON U1.USER_ID = P.PROJECT_LEADER
LEFT JOIN PARTICIPANTS T ON T.PROJECT_ID = P.PROJECT_ID
LEFT JOIN USERS U2 ON U2.USER_ID = T.USER_ID
GROUP BY P.PROJECT_ID

I made an SQLFiddle (here) to which I added a bit more data than you had. This is the output:

PROJECT_NAME    PROJECT_LEADER  PARTICIPANTS
MY PROJECT      ADAM            JOHN,BRIAN
UPDATE THIS     ADAM            (null)
STATUS FIX      BRIAN           ADAM

Upvotes: 1

Related Questions