Question 21384 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: Counting results from multiple LEFT JOINS

I have three tables that describe data where there are some main things. Each thing can have several categories below it, and each category can have multiple segments.

main

main_id main_name
1 bla
2 blub

categories

cat_id main_id category_name
4 1 bla cat
5 1 blub cat
6 2 ble cat
7 2 blib cat

segments

seg_id cat_id segment_name
10 4 bla seg
11 4 blub seg
12 5 bli seg
13 6 blob seg

I’m augmenting the main table with a column that shows the number of categories below each main thing, as well as the total number of segments in all categories belonging to it.

main_id main_name n_cats n_segs
1 bla 2 3
2 blub 2 1

I tried with the following SQL statement:

SELECT
    m.main_id, m.main_name, count(c.cat_id) as n_cats, count(s.seg_id) as n_segs
FROM main
    LEFT JOIN categories c ON c.main_id = m.main_id
    LEFT JOIN segments s ON s.cat_id = c.cat_id
GROUP BY m.main_id

But it yields the same values for n_cats and n_segs, instead of different ones as desired:

main_id main_name n_cats n_segs
1 bla 2 2
2 blub 2 2
Tags:

2

Answers


  1. 0

    It looks like you need this SELECT clause using COUNT(DISTINCT col).

    SELECT m.main_id, m.main_name, 
       count(DISTINCT c.cat_id) as n_cats, 
       count(DISTINCT s.seg_id) as n_segs

    This will undo the combinatorial explosion caused by your JOINs.

    Login or Signup to reply.
  2. 0

    I executed you SQL with same data set,

    SELECT
    m.main_id, m.main_name, COUNT(c.cat_id) AS n_cats, COUNT(s.seg_id) AS n_segs
FROM main m
    LEFT JOIN categories c ON c.main_id = m.main_id
    LEFT JOIN segments s ON s.cat_id = c.cat_id
GROUP BY m.main_id

    I am getting this result, the only difference is I use m as an alias FROM main m

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search