Mysql Join AND SUM

Question

Please look to this picture

http://i.stack.imgur.com/A2RB2.jpg

i want to get

1. sum of money in table come
2. sum of money in table leave
3. inner join emp using id

Thats my query

SELECT uid,SUM(money) FROM come 
INNER JOIN emp ON(come.uid = emp.id) 
WHERE emp.statue=1 
GROUP BY come.uid 
UNION 
SELECT uid,SUM(money) FROM `leave` 
INNER JOIN emp ON ( leave.uid = emp.id ) 
GROUP BY leave.uid 

and the result was this image

http://i.stack.imgur.com/xZtO3.jpg

Answer 1

Another way to do it (not tested, so let me know if it blows up):

SELECT emp.id, IF(emp.statue=1, c.sumCome, 0) AS sumCome, l.sumLeave
FROM emp
     LEFT JOIN (SELECT uid, SUM(money) AS sumCome
                FROM come
                GROUP BY uid
               ) c ON emp.id = c.uid
     LEFT JOIN (SELECT uid, SUM(money) AS sumLeave
                FROM leave
                GROUP BY uid
               ) l ON emp.id = l.uid

I don't know which'll be faster compared to Kevin's. You might want to explain them both and see.

Answer 2

Try this

   select uid,sum(ComeTot),sum(LeaveTot)
   from
  (

    SELECT uid,SUM(money) as ComeTot ,0 as LeaveTot
    FROM come 
    INNER JOIN emp ON(come.uid = emp.id) WHERE emp.statue=1 
    GROUP BY come.uid 
    UNION 
    SELECT uid,0 ,SUM(money) FROM leave
    INNER JOIN emp ON ( leave.uid = emp.id ) 
    GROUP BY leave.uid
  ) xx

GROUP BY xx.uid

I don't have mySQL installed on this PC, but this should get you close. Depending on the result of the above, you might need to wrap the entire query above in another group by...

Answer 3

here we go:

SELECT q1.uid,SUM(q1.l),SUM(q1.c) FROM
(
SELECT uid,0 as l,SUM(money) as c FROM come 
INNER JOIN emp ON (come.uid = emp.id) 
WHERE emp.statue=1 GROUP BY come.uid 
UNION ALL 
SELECT uid,SUM(money) as l,0 as c FROM leave
INNER JOIN emp ON ( leave.uid = emp.id ) GROUP BY leave.uid 
) q1
GROUP BY uid