Query MySQL with join, count and left group with 3 tables

advertisements

I have 3 tables where I am trying to put a join query like below.

The below is the Section table containing 3 sections.

Section
*****************************
* section_id * section_name *
*****************************
*  1         * A            *
*  2         * B            *
*  3         * C            *
*****************************

The below is the section_subject table. 1st section contains 2 subjects, 2nd section contains 2 subjects and 3rd section contains 3 subjects.

Section_Subject
***********************************
* ss_id * section_id * subject_id *
***********************************
* 1     * 1          * 8          *
* 2     * 1          * 9          *
* 3     * 2          * 6          *
* 4     * 2          * 5          *
* 5     * 3          * 2          *
* 6     * 3          * 3          *
* 7     * 3          * 4          *
***********************************

The below is the section_batch table. 3rd section alone contains 2 batches

Section_Batch
*********************************
* sb_id * section_id * batch_id *
*********************************
* 1     * 3          * 6        *
* 2     * 3          * 7        *
*********************************

I want a query to yield the below result

**************************************************************
* section_id * section_name * count_subjects * count_batches *
**************************************************************
* 1          * A            * 2              * 0             *
* 2          * B            * 2              * 0             *
* 3          * C            * 3              * 2             *
**************************************************************

I know that we can do some kind of sub query and achieve the above result. But how to get the result using left join and group query?


I believe using count(distinct) will get you what you need. You have to use distinct because the joins have a multiplier effect where a section has more than one subject and more than one batch.

select
    s.section_id,
    min(t1.section_name) as section_name,
    count(distinct ss.subject_id) as subject_count,
    count(distinct sb.batch_id) as batch_count,
from
    Section as s
    left join Section_Subject as ss on ss.section_id = s.section_id
    left join Section_Batch as sb on sb.section_id = s.section_id
group by
    s.section_id

By the way, I think the left joins could probably be inner joins.