mysql - Second inner join doubles count result -

i have tables a, b , c. has many b , c. have 6 b records , half of them marked deleted (deleted_at != null).

when select query

select a.id, count(b.id) table_a  inner join table_b b on a.id = b.a_id , b.deleted_at null a.id = 5; 

answer correct (id: 5, count: 3)

but when add 1 more inner join

select a.id, count(b.id) table_a  inner join table_b b on a.id = b.a_id , b.deleted_at null inner join table_c c on a.id = c.a_id , c.deleted_at null a.id = 5; 

it doubles count result (id:5, count: 6)

how can fix it?

as othered have mentioned can count distinct b ids here.

however, problem quite common when 1 needs aggregates various tables. problems occur, because people join records , try aggregates.

for instance:

select a.id, sum(b.value) table_a  inner join table_b b on a.id = b.a_id , b.deleted_at null inner join table_c c on a.id = c.a_id , c.deleted_at null a.id = 5; 

this same query yours. here cannot use distinct anymore, because if had values 100, 200, 200 in b, 300 instead of 500 then. general solution is: join shall joined. here 2 solutions above query:

solution 1: aggregate before join:

select a.id, b.total table_a  inner join  (    select a_id, sum(value) total     table_b     deleted_at null    group a_id ) b on a.id = b.a_id inner join table_c c on a.id = c.a_id , c.deleted_at null a.id = 5; 

solution 2: if need 1 value table, in select clause:

select a.id,  (    select sum(value)    table_b     deleted_at null    , a_id = a.id )  total  table_a  inner join table_c c on a.id = c.a_id , c.deleted_at null a.id = 5;