How to fetch names having multiple values in a single query in sql [duplicate]

Question

I have two tables: Person, PersonType

    Person                         PersonType                          PersonTypeMapping
personId   name                 typeId     typeName                tId   personId   typeId
  101         p1                  1        Student                 123       101       1
  102         p2                  2        Employee                124       102       1
  103         p3                                                   125       102       2
                                                                   126       103       1
                                                                   127       103       2

Now I need to fetch the person names and their corresponding typeNames.

This is my query

SELECT Person.personId, Person.name,PersonType.typeName 
from Person 
    INNER JOIN PersonTypeMapping ON PersonTypeMapping.personId = Person.personId 
    INNER JOIN PersonType ON PersonType.typeId = PersonTypeMapping.typeId;

My Output:

   personId       name        typeName
   101             p1          Student
   102             p2          Student
   102             p2          Employee
   103             p3          Student
   103             p3          Employee

But I need output like this:

   personId       name        typeName
   101             p1          Student
   102             p2          Student, Employee
   103             p3          Student, Employee

What can be done for this?

SELECT Person.personId, Person.name,PersonType.typeName, GROUP_CONCAT(PersonType.typeName SEPARATOR ', ') FROM Person INNER JOIN PersonTypeMapping ON PersonTypeMapping.personId = Person.personId INNER JOIN PersonType ON PersonType.typeId = PersonTypeMapping.typeId; GROUP BY PersonType.typeName — user3485442
– user3485442, Commented Oct 28, 2019 at 10:26

Booboo · Accepted Answer · 2019-10-28 10:24:04Z

0

SELECT Person.personId, Person.name, Group_Concat(PersonType.typeName) 
from Person 
    INNER JOIN PersonTypeMapping ON PersonTypeMapping.personId = Person.personId 
    INNER JOIN PersonType ON PersonType.typeId = PersonTypeMapping.typeId
GROUP BY Person.personId, Person.name;

answered Oct 28, 2019 at 10:24

Booboo

45.7k4 gold badges46 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to fetch names having multiple values in a single query in sql [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related