MySQL how to make FULL OUTER

Question

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;

what is the output you got – Pathik Vejani Jan 05 '16 at 09:02 — Pathik Vejani, Jan 05 '16 at 09:02
create a sqlfiddle to get quick help. – Zafar Malik Jan 05 '16 at 09:22 — Zafar Malik, Jan 05 '16 at 09:22

Saharsh Shah · Accepted Answer · 2016-01-07T06:01:59.557

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;

[Err] 1054 - Unknown column 'ClassSTD' in 'group statement' . I removed ClassSTD from group. I got the result but All Sections are not showing ... but I got all class .... I want all sections as well — fresher, Jan 05 '16 at 09:26
checked error is gone. BUT still I am not seeing all the section. There are section and subject is not allocated to section yet. Class ->has->section ->has->subject I want all Class (with or without section) - Getting it I want all Section (with or without subject) - Not Getting it — fresher, Jan 05 '16 at 09:32
is it possible to to ascending order on Main subjectName and Optional subjectName col return? — fresher, Jan 06 '16 at 17:33

MySQL how to make FULL OUTER

1 Answers1