CODEWITHSUNDEEP
mysql
nnRg
nnRg

Reputation: 3

MySQL Join multiple rows in one column with condition

Tables

Person

ID|NAME |SURNAME
1 |James|Smith
2 |Jack |Sparrow

PeopleDepartment

IDPERSON|IDDEPARTMENT
1       |1
1       |2
2       |2

Department

ID|NAME
1 |customer_service
2 |store

Problem

What I need to do is select people and its departments, filtering by one or more departments. Now I'm able to select all the people and their departments, but I can't filter the results by departments name:

SELECT person.name, person.surname, GROUP_CONCAT(distinct department.name SEPARATOR ', ') as departments
LEFT JOIN peopledepartment ON person.id=department.idperson
INNER JOIN department ON peopledepartment.iddepartment=department.id

And the output with that query is:

NAME |SURNAME|DEPARTMENTS
James|Smith  |customer_service, store
Jack |Sparrow|store

I need to filter that result by department.name='customer_service', so the result is only:

NAME |SURNAME|DEPARTMENTS
James|Smith  |customer_service, store

Any ideas? Thanks in advance!

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Use a having clause:

SELECT p.name, p.surname,
       GROUP_CONCAT(distinct d.name SEPARATOR ', ') as departments
FROM person p INNER JOIN
     peopledepartment pd
     ON p.id = pd.idperson INNER JOIN
     department d
     ON pd.iddepartment = d.id
GROUP BY p.name, p.surname
HAVING SUM(d.name = 'customer_service') > 0;

Notes:

  • The distinct is probably not necessary in group_concat().
  • Use table aliases. These make it easier to write and read the query.
  • You seem to have left out the from clause in your question.
  • The having clause counts the number of rows that match the given department. The > 0 means that at least one such match exists.

Upvotes: 2

Related Questions