I am having a problem getting my resultset to come out right. I am
looking to join 3 tables getting a sum out of two and data out of the
the third:
Tables:
users
debits
credits
These tables have a common userid key and I am looking to have the
resultset look like:
name email sum(credits) sum(debits)
The data looks something like:
users-
123 Joe jo*@joe.com 123Main
debits-
123 10
123 20
credits
123 3
123 6
123 9
The problem I am having is that a UNION gives me:
Joe jo*@joe.com 30 0
Joe jo*@joe.com 0 18
My Join looks like this:
Joe jo*@joe.com 90 36
What I want is:
Joe jo*@joe.com 30 18
Here is my real SQL:
mysql>
SELECT first_name, sum(c.credits), sum(d.debits)
FROM users
LEFT OUTER JOIN all_credits c USING (userid)
LEFT OUTER JOIN all_debits d ON ( orders.userid=users.userid )
group by users.userid;
OR
mysql>
( select first_name, sum(c.credits), 0 from users
LEFT OUTER JOIN all_credits c using (userid) group by users.userid)
UNION ( select first_name, 0, sum(d.debits) from all_debits d
LEFT OUTER JOIN orders using (userid) group by users.userid) ;
Thanks for any help.
Mark