multiple mysql JOINs with Aggregate functions

[lemonevan]

Hi,

I'm getting a strange result when nesting 2 outer joins on data that includes an aggregate function. Using MySQL, here is my query:

SELECT a.user_id
SUM(b.points) as points1,
SUM(c.points) as points2
FROM usertable a
LEFT OUTER JOIN usertable1 b ON (a.user_id=b.user_id)
LEFT OUTER JOIN usertable2 c ON (a.user_id=c.user_id)
GROUP BY a.user_id

Note that usertable1 and usertable2 can have multiple entries for any given user_id (hence my trying to SUM the totals).

When I run this query, I end up getting strange results from the aggregate functions (the values are much higher than they should be). Note that this doesn't happen when I only have 1 LEFT JOIN - it only begins to occur when I add the second one. If anyone can point out what I'm doing wrong in my query, it would be greatly appreciated.

 thanks!

   - le

[Lars_Berg]

Not so strange if you have multiple entries for a user in both usertable1 and usertable2. You can compare a join with multiplication (2 entries in usertable1 1 in usertable and 2 in usertable2 will result in 212 values being summed)

SELECT a.user_id 
SUM(b.points) as points1,'Usertabel1 points' FROM usertable a 
LEFT OUTER JOIN usertable1 b ON (a.user_id=b.user_id) 
group by a.user_id
union
select a.user_id,sum(c.points),'Usertable2 points' from usertable a
LEFT OUTER JOIN usertable2 c ON (a.user_id=c.user_id) 
GROUP BY a.user_id 

[lemonevan]

Thanks for the info, but unfortunately MySQL doesn't support UNION operations.... 😕 Could you offer any other solutions?

[Sxooter]

Three options:

1: Look for it in a beta MySQL
2: Replace MySQL with almost any other data in the universe, they all support unions.
3: Use a temp table

i.e.

select sum(field) into temp tablename ....
insert into temp tablename (select sum(field)....