MySQL: Counting results from multiple LEFT JOINS-CodePudding

I have three tables like this: They 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

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 now looking for augmenting the main table with a column that shows the amount of categories that below each main thing, as well as the total number of segments in all categories belonging to it.

So what I am looking for is something like this:

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, i.e. something like this:

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

Any pointers? Apologies if the question is not formulated well, this is my first post here.

Thanks!

CodePudding user response：

It looks like you need this SELECT clause using