PHP Join three tables and group using GROUP_CONCAT or other way

Question

So i have three tables:

1. Workers - id, name and other info
2. Projects - id, name and other info
3. Workers_projects - worker_id, project_id

Table Workers_projects stores which worker is working on which project (by ids)

The goal:

In some cases a couple of workers will be working on the same project. I would like to make two outputs:

1. Project 1 - Worker 1, Worker 2, Worker 3
2. Project 2 - Worker 2, Worker 3.

SQL selects all projects in column 1 and lists every worker working on it in column 2.

and

1. Worker 1 - Project 1, Project 2;
2. Worker 2 - Project 5, Project 6.

SQL selects all workers in column 1 and list every project that the worker is working on in column 2.

What i have got so far:

SELECT p.name, GROUP_CONCAT(w.name)

FROM workers_projects as wp 

LEFT JOIN projects as p ON wp.project_id=p.id

LEFT JOIN workers as w ON wp.worker_id=w.id

What i does - it takes random project and then lists all workers.. Im quite new to SQL so any help is appreciated.

Thanks!

Answer 1

The code you've posted looks very close to your desired output. Is this what you're after?

# Projects with workers
SELECT p.name AS project, GROUP_CONCAT(w.name SEPARATOR ', ') AS workers
FROM projects p
LEFT JOIN workers_projects wp ON (p.id = wp.project_id)
LEFT JOIN workers w ON (w.id = wp.worker_id)
GROUP BY p.id

# Workers with projects
SELECT w.name AS worker, GROUP_CONCAT(p.name SEPARATOR ', ') AS projects
FROM workers w
LEFT JOIN workers_projects wp ON (w.id = wp.worker_id)
LEFT JOIN projects p ON (p.id = wp.project_id)
GROUP BY w.id

If you want to only show projects with workers, or likewise workers with projects, you could use an INNER JOIN instead of a LEFT JOIN.

Here's an example:

SELECT p.name AS project, GROUP_CONCAT(w.name SEPARATOR ', ') AS workers
FROM projects p
INNER JOIN workers_projects wp ON (p.id = wp.project_id)
INNER JOIN workers w ON (w.id = wp.worker_id)
GROUP BY p.id