CODEWITHSUNDEEP
mysql
Dom
Dom

Reputation: 3436

MYSQL, is this kind of request possible?

I have persons (table person) who have 0 or N roles (tables role and personne_role). I want to select all the persons , with the roles they have, to have this kind of result :

PHIL COLLINS | Drummer | Singer

MIKE RUTHERFORD | Singer

ION ANDERSON | Singer

MIKE JAGGER |

CARLOS SANTANA | Guitarist
......

Each line can have 0 or N roles.

To do that, I make 2 requests

It works BUT in the case of there are a lot of lines, it is not very efficient.

I would like the same result in 1 request.

Is it possible ? What are the mysql keywords I must use to do that ?

Thanks for your feedback. dominique

Upvotes: 0

Views: 49

Answers (1)

Matteo Tassinari
Matteo Tassinari

Reputation: 18584

You could use a JOIN with a GROUP_CONCAT, something like:

SELECT person.name, role.roles
FROM person
LEFT JOIN (
  SELECT person_id, GROUP_CONCAT(DISTINCT role SEPARATOR ' | ') roles
  FROM person_role
  GROUP BY person_id
) role ON (person.id = role.person_id)

EDIT: the fields name are just a guess, since you didn't show us the full table schema; also, if the roles are actually in a separate tale, say joined by a role_id, you'd need to add it to the subquery.

Upvotes: 2

Related Questions