MYSQL comma base separated select result

Question

I have 4 tables, as below.

Table: Class

ClassID     |   ClassSTD
--------------------------------
1           |   STD-1<br>
2           |   STD-2

Table: Section

SectionId   |   SectionName | ClassId
--------------------------------------------
1           |   sec-A       | 1
2           |   sec-B       | 1
3           |   sec-C       | 1
4           |   sec-A       | 2
5           |   sec-B       | 2
6           |   sec-C       | 2

Table: Subject

subjectId   |   subjectName
------------------------------------
1           |   Art
2           |   Music
3           |   Play

Table SubjectAllocationToClass

    classId         |   sectionID           |   subjectId   | type
-----------------------------------------------------------------------
1(STD-1)        |   1(sec-A)            |   1(Art)      | main
1(STD-1)        |   2(sec-B)            |   1(Art)      | main
1(STD-1)        |   3(sec-C)            |   1(Art)      | optional
1(STD-1)        |   1(sec-A)            |   2(Music)    | main
1(STD-1)        |   2(sec-B)            |   2(Music)    | optional

Above table "SubjectAllocationToClass" shows distribution of two type of subject (Main and optional) to section for class. How I can achieve below result from SELECT statement?

classSTD |  sectionName | Main subjectName   | Optional subjectName
-----------------------------------------------------------------------------
STD-1    |  sec-A       | Art, Music         |
STD-1    |  sec-B       | Art                |  Music
STD-1    |  sec-C       |                    |  Art

Yes it is possible and it is called GROUP_CONCAT agg function. The question is what did you tried so far? — Lukasz Szozda
– Lukasz Szozda, Commented Dec 28, 2015 at 12:29

Saharsh Shah · Accepted Answer · 2015-12-30 07:40:58Z

1

Use GROUP_CONCAT() function:

Try this:

SELECT D.classSTD, 
       C.sectionName, 
       GROUP_CONCAT(B.subjectName SEPARATOR ', ') AS subjectName
FROM SubjectAllocationToClass A 
INNER JOIN Subject B ON A.subjectId = B.SubjectId 
INNER JOIN Section C ON A.sectionId = C.SectionId   
INNER JOIN Class D ON A.classID = D.ClassID 
GROUP BY D.ClassID, C.SectionId;

For your another question

SELECT D.classSTD, 
       C.sectionName, 
       GROUP_CONCAT(B.subjectName SEPARATOR ', ') AS subjectName, 
       GROUP_CONCAT(CASE WHEN B.type = 'main' THEN B.subjectName ELSE NULL END SEPARATOR ', ') AS mainsubjectName, 
       GROUP_CONCAT(CASE WHEN B.type = 'optional' THEN B.subjectName ELSE NULL END SEPARATOR ', ') AS optionalSubjectName
FROM SubjectAllocationToClass A 
INNER JOIN SUBJECT B ON A.subjectId = B.SubjectId 
INNER JOIN Section C ON A.sectionId = C.SectionId   
INNER JOIN Class D ON A.classID = D.ClassID 
GROUP BY D.ClassID, C.SectionId;

edited Dec 30, 2015 at 7:40

answered Dec 28, 2015 at 12:38

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

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

2 Comments

Saharsh Shah Over a year ago

Why downvote? Give the reason so others can find what is the wrong in my query???

fresher Over a year ago

Can you please help me, I edited the condition in main description

Gouda Elalfy · Accepted Answer · 2015-12-28 12:39:13Z

1

you can do this by group_concat like this:

select classSTD , SectionName ,  group_concat(`subjectName` separator ',') as `subjectName` 
FROM SubjectAllocationToClass 
INNER JOIN Class ON SubjectAllocationToClass.classId = Class.ClassID 
INNER JOIN Section ON SubjectAllocationToClass.sectionID   = Section.sectionId
INNER JOIN Subject ON SubjectAllocationToClass.subjectID   = Subject .subjectId  
GROUP BY  SubjectAllocationToClass.subjectID ;

answered Dec 28, 2015 at 12:39

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

Comments

Ninju · Accepted Answer · 2015-12-28 12:45:57Z

0

USE GROUP_CONCAT

 SELECT  sec.SectionName , cl.classSTD, GROUP_CONCAT(`sub.subjectName`) as `subjectName` FROM SubjectAllocationToClass AS al JOIN Class AS cl ON al.classId = cl.ClassID JOIN Section AS sec ON al.sectionID  = sec.sectionId JOIN Subject AS sub ON al.subjectID   = sub.subjectId  
GROUP BY  al.subjectID;

answered Dec 28, 2015 at 12:45

Ninju

2,5322 gold badges16 silver badges21 bronze badges

Collectives™ on Stack Overflow

MYSQL comma base separated select result

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related