0

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:

  • One to many relationship
  • Loops
  • Arrays
  • Joins

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.

Nick
  • 138,499
  • 22
  • 57
  • 95
nikoh
  • 101
  • 10

1 Answers1

1

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
Nick
  • 138,499
  • 22
  • 57
  • 95
  • Wow thanks Nick! Work's like a charm. Tried to figure out GROUP_CONCAT but didn't really follow untill now, and with that SQLFiddle. Thanks again! – nikoh Sep 04 '18 at 08:28
  • Would there be possible to GROUP the output of the GROUP_CONCAT / Participants if it would be a scenario were it would be a duplicate of the user participant with the same user id in the DB: Participants? And how to do that? – nikoh Sep 04 '18 at 11:53
  • Adding DISTINCT in the GROUP_CONCAT fixed the issue :) – nikoh Sep 04 '18 at 11:59
  • 1
    Only just saw your comment. Sounds like I didn't need to anyway! :) – Nick Sep 04 '18 at 12:58
  • Hehe no worries, got stuck on another one now though. Say for example that It's possible for the project leader also to be a participant, and I'd like to not show the project leader in the participants list also. Is that possible to do with this sort of query? And also, I added an SEPARATOR in the GROUP_CONCAT(). And would like to separate each participant with
    , this works. But I would like to add the class to the "start"
    again and it dosn't take " or ', idéas?
     – nikoh Sep 04 '18 at 13:25
  • 1
    I think you can do the first part by changing the `ON` condition for the `JOIN` to `U2` to be `U2.USER_ID = T.USER_ID AND U2.USER_ID != P.PROJECT_LEADER`. For the second part you should be able to escape quotes with `\\` – Nick Sep 04 '18 at 13:35
  • Wow thanks Nick! You're brilliant! The first part worked great, I'm trying to figure out the second part now but having some trouble. Will try some first by myself before asking. Thanks again! – nikoh Sep 04 '18 at 13:42
  • No worries, Got the " to work with \", found the answer for that one here: https://stackoverflow.com/questions/881194/how-do-i-escape-special-characters-in-mysql – nikoh Sep 04 '18 at 13:58
  • But now I realised that the current query is outputing an "empty" line with the PROJECT_OWNER in the PARTICIPANTS list. Trying to work on that :) – nikoh Sep 04 '18 at 13:59