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

Question

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?

radar · Accepted Answer · 2015-04-23 21:24:22Z

2

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

answered Apr 23, 2015 at 21:24

radar

13.4k2 gold badges27 silver badges33 bronze badges

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

3 Comments

Malaiselvan Over a year ago

Your solution works but I am not sure about the performance of this query since it executes multiple selects and group by.

ypercubeᵀᴹ Over a year ago

@Malaiselvan You can test. This type of solution is often more efficient. Esepcially if you have more than 2 tables joined to the base table. Imagine there are 6 instead of 2. I'd bet on this type of 6 separate group by and then 6 joins than a huge 7-table join and then group by.

ypercubeᵀᴹ Over a year ago

See my answer in a similar question at the sister site, dba.se: Help with this query

shawnt00 · Accepted Answer · 2015-04-23 21:39:24Z

1

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.

edited Apr 23, 2015 at 21:39

answered Apr 23, 2015 at 21:24

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Comments

souslov · Accepted Answer · 2015-04-23 22:00:58Z

1

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

edited Apr 23, 2015 at 22:00

answered Apr 23, 2015 at 21:13

souslov

44.7k11 gold badges92 silver badges113 bronze badges

1 Comment

Malaiselvan Over a year ago

@notulysees The count_subjects & count_batches for the 3 section shows 6 which is wrong.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related