CODEWITHSUNDEEP
phpsqlgroup-concat
Benua
Benua

Reputation: 269

PHP Join three tables and group using GROUP_CONCAT or other way

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!

Upvotes: 0

Views: 108

Answers (1)

fubar
fubar

Reputation: 17398

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

Upvotes: 1

Related Questions