Skip to content

Instantly share code, notes, and snippets.

@anovsiradj
Last active Aug 26, 2015
Embed
What would you like to do?
method that work for: mysql left join that count joined table
-- work, but the result not accurate
SELECT c.user_id,
c.user_fname,
COUNT(cc.user_id) AS total_child,
COUNT(ccc.user_id) AS total_consul
FROM drpp_user c
LEFT JOIN drpp_child cc
ON cc.user_id = c.user_id
LEFT JOIN drpp_consul ccc
ON ccc.user_id = c.user_id
GROUP BY c.user_id
-- current that i use
SELECT u.*, c.*, s.*
FROM drpp_user u
LEFT JOIN (
SELECT user_id, COUNT(*) AS sum_child
FROM drpp_child
GROUP BY user_id
) c
ON c.user_id = u.user_id
LEFT JOIN (
SELECT user_id, COUNT(*) AS sum_consul
FROM drpp_consul
GROUP BY user_id
) s
ON s.user_id = u.user_id
-- i think this just like v3
select
t.user_id,
t.user_fname,
s.sum_child,
c.sum_consul
from
drpp_user t
left join (
select
user_id,
count(*) as sum_child
from drpp_child
group by user_id
) s ON s.user_id = t.user_id
left join (
select
user_id,
count(*) as sum_consul
from drpp_consul
group by user_id
) c ON c.user_id = t.user_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment