CODEWITHSUNDEEP
mysqlsqlselectjoinleft-join
fresher
fresher

Reputation: 399

MySQL how to make FULL OUTER

I have 4 tables, as below.

Table: Class

ClassID     |   ClassSTD
--------------------------------
1           |   STD-1
2           |   STD-2
3           |   STD-3
4           |   STD-4

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
7           |   sec-A       | 3

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.

Need All Class irrespective of section or subjectAllocation Need All Section irrespective of subjectAllocation

I trying making Left JOIN, Right JOIN but not able to get desire.

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
    STD-2    |              |                    |
    STD-3    |  sec-A       |                    |
    STD-4    |              |                    |



select 
  ClassSTD as ClassSTD, 
  sectionname AS SectionName, 
  COALESCE(GROUP_CONCAT(CASE WHEN sac.type = 'main' THEN subjectName END), '')  as 'Main subjectname',
  COALESCE(GROUP_CONCAT(CASE WHEN sac.type = 'optional' THEN subjectName END), '')  as 'Optional subjectname'
FROM SubjectAllocationToClass sac
JOIN  Class c  ON c.classid = sac.classid
Left JOIN Section sc ON sc.sectionid = sac.sectionid
Left JOIN Subject sj ON  sj.subjectid = sac.subjectid
GROUP BY ClassSTD, SectionName;

Upvotes: 1

Views: 70

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

There is no need to use FULL OUTER JOIN, You can directly achieve using LEFT JOIN and SUBQUERY

Try this: 

SELECT C.ClassSTD, 
       COALESCE(A.SectionName, '') AS SectionName, 
       COALESCE(A.Mainsubjectname, '') AS 'Main subjectname',
       COALESCE(A.Optionalsubjectname, '') AS 'Optional subjectname'
FROM Class C
LEFT JOIN ( SELECT sc.classId AS classId, 
                   sc.sectionname AS SectionName, 
                   COALESCE(GROUP_CONCAT(CASE WHEN sac.type = 'main' THEN subjectName END), '')  AS Mainsubjectname,
                   COALESCE(GROUP_CONCAT(CASE WHEN sac.type = 'optional' THEN subjectName END), '')  AS Optionalsubjectname
            FROM Section sc
            LEFT JOIN SubjectAllocationToClass sac ON sc.sectionid = sac.sectionid
            LEFT JOIN SUBJECT sj ON  sj.subjectid = sac.subjectid
            GROUP BY sc.classId, sc.SectionName
          ) AS A ON C.classId = A.classId;

For your second query:: 

SELECT C.ClassSTD, 
       COALESCE(A.SectionName, '') AS SectionName, 
       COALESCE(A.Mainsubjectname, '') AS 'Main subjectname',
       COALESCE(A.Optionalsubjectname, '') AS 'Optional subjectname'
FROM Class C
LEFT JOIN ( SELECT sc.classId AS classId, 
                   sc.sectionname AS SectionName, 
                   COALESCE(GROUP_CONCAT((CASE WHEN sac.type = 'main' THEN subjectName END) ORDER BY subjectName), '')  AS Mainsubjectname,
                   COALESCE(GROUP_CONCAT((CASE WHEN sac.type = 'optional' THEN subjectName END) ORDER BY subjectName), '')  AS Optionalsubjectname
            FROM Section sc
            LEFT JOIN SubjectAllocationToClass sac ON sc.sectionid = sac.sectionid
            LEFT JOIN SUBJECT sj ON  sj.subjectid = sac.subjectid
            GROUP BY sc.classId, sc.SectionName
          ) AS A ON C.classId = A.classId;

Upvotes: 1

Related Questions