sql - MYSQL: Multiple Table Join - Conditional on previous join -

sql - MYSQL: Multiple Table Join - Conditional on previous join -

members_table member_id --------------------------------------------- 1 accounts_table account_id member_id --------------------------------------------- 1 1 investments_table investment_id account_id --------------------------------------------- 1 1 2 1 funds_table fund_id investment_id --------------------------------------------- 1 1 2 2

this current query:

select m.member_id, a.account_id, i.investment_id, f.fund_id, count(a.account_id) member_accounts_total, count(i.investment_id) member_investments_total, count(f.fund_id) member_funds_total members m left bring together accounts on m.member_id = a.member_id left bring together investments on a.account_id = i.account_id left bring together funds f on f.fund_id = i.fund_id

i see next results: member_accounts_total: 1 member_investments_total: 2 member_funds_total: 2

instead, getting these results: member_accounts_total: 2 member_investments_total: 2 member_funds_total: 2

i don't want write multiple queries this.

just need change count(a.account_id) member_accounts_total,

to count( distinct a.account_id) member_accounts_total,

the reason you're getting 2 because left bring together on accounts investments results in 2 records. distinct count of members need add together well... distinct.

note may have problems other totals (distinct may needed there in long run...) if fellow member had multiple accounts. may odd counts (if each business relationship had same investment... want see count 1 time or twice?

mysql sql join