CODEWITHSUNDEEP
mysqlsqlgroup-byleft-join
Malaiselvan
Malaiselvan

Reputation: 1125

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

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?

Upvotes: 1

Views: 552

Answers (3)

potashin
potashin

Reputation: 44581

You can use count with distinct:

select t1.section_id
     , t1.section_name
     , count(distinct t2.subject_id) as count_subjects
     , count(distinct t3.batch_id) as count_batches
from Section t1
left join Section_Subject t2 on t1.section_id = t2.section_id
left join Section_Batch t3 on t1.section_id = t3.section_id
group by t1.section_id
       , t1.section_name

SQLFiddle

Upvotes: 1

shawnt00
shawnt00

Reputation: 17915

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.

Upvotes: 1

radar
radar

Reputation: 13425

you need to do left join and group by each table separately to get the count and then do a join between them

SQL Fiddle: http://www.sqlfiddle.com/#!9/ea4ee/12

select T1.section_id,
       T1.section_name,
       T1.subjects_count,
       T2.batch_count
FROM (
select S.section_id, 
       S.section_name,
       COUNT(SS.subject_id) as subjects_count
from Section S
LEFT JOIN Section_Subject SS
on S.section_id = SS.section_id
group by S.section_id, S.section_name )T1
LEFT JOIN (
select S.section_id, 
       S.section_name,
       COUNT(SB.batch_id ) as batch_count
from Section S
LEFT JOIN Section_Batch SB
on S.section_id = SB.section_id
group by S.section_id, S.section_name
  ) T2
 on T1.section_id = T2.section_id

Upvotes: 2

Related Questions