SQl JOIN thinks weird?

Question

MySQL

Guys, hello.
I ran into a problem, JOIN strangely counts the number of records in tables.
Task: you need to count the number of records with the desired id in several tables.
I am building a query, it seems to count, but it is the output that does not form correctly.

SELECT t.id,t.master_theme, t.theme, COUNT(post.theme_id) as post_theme, COUNT(video.theme_id) as video_theme, COUNT(subscribe_theme.theme_id) as subscribe_theme 
FROM `theme` as t 
LEFT OUTER join post ON t.id = post.theme_id 
LEFT OUTER join video ON t.id = video.theme_id 
LEFT OUTER join subscribe_theme ON t.id = subscribe_theme.theme_id 
WHERE t.id in (88,14,91,23,79,13,21,24,89,17,65,35,97,82,37,38,85,64,56,90,60,99,16,76) 
GROUP BY t.id

Conclusion:

The bottom line is that in the line id=35 subscribe_theme actually has only one entry, but displays two. THAT is as if it copies the value from post_theme.
Another strange thing arises when the number of lines in subscribe_theme is actually 2

THEN the total output already gets 4:

Answer 1

JOIN - Cartesian multiplication.
One line from `theme`, two from `post s` to it, zero from `video` is ignored (LEFT), one from `subscribe`
Multiply, 1*2*1*1 = 2, which is what you get.
If there are two lines in `subscribe`, we get 1*2*1*2 = 4

Answer 2

For it to work correctly, you need something like this:

select
count(a.id) as all_a,
sum(ifnull(b.id, 0)) as all_b,
sum(ifnull(c.id, 0)) as all_c
from a left join b using(b_join) left join c using(c_join)
group by a.id