I have 2 different tables whose purpose is to store the points gotten by a group of users in a football game contest.

The 1st table stores the points gotten by the user for a prediction of the team players who will eventually play a game, and the 2nd one stores the points for a prediction of the final score of the game.

So both tables will store as many rows as users who predict, times as many games that have been played or to be played (one row of each football game for each user).

An example of how indistinctly one of these tables might look supposing that there are only three users and only three games:

The other table would look similarly but will store different points.

What I want to get is a ranking with the total score of every user by adding up their stored points. I've been trying with several queries but I don't seem to get the expected result. For instance, this is the query I considered more accurate to what I wanted, but the totals are much bigger than expected:

SELECT ROUND(SUM(p.points) + SUM(r.points)) AS total, p.userid
FROM prediction_players p
INNER JOIN prediction_results r
ON p.userid = r.userid AND status=1
GROUP BY a.userid
ORDER BY total DESC

Note: the status field stored whether the corresponding game has already been played (1) or not (0). It must therefore be = 1 because otherwise points=NULL

a sure sign of cross join effects

the solution is not to join the tables

Thanks a lot r937!

I've found this other solution but I'm not sure whether it would get the same result or not, and whether it would be better or worse for a mysql server:

What do you think about this one?

that should be pretty easy to find out by testing


it will ignore the prediction_results score for any user who has no prediction_players score

Yes, you're right! Fortunately this application I'm using ensures that everyone who does a prediction for players, does it too for the game final score, in all cases.

Yet I've found another solution which implies JOINS. It would be just the same as the one I started with, but I was missing the statement in bold characters:

SELECT ROUND(SUM(p.points) + SUM(r.points)) AS total, p.userid
FROM prediction_players p
INNER JOIN prediction_results r
ON p.userid = r.userid AND status=1 AND p.gameid = r.gameid
GROUP BY a.userid
ORDER BY total DESC

I suppose I would have to do a LEFT JOIN or just a JOIN to prevent the result from the same problem you said in your last post, wouldn't I?

no

also, a JOIN is an INNER JOIN by default (i always write the syntax out in full, that way there's no doubt about what i meant to do)

the real wtf is why there are separate tables for the predictions

So the previous query, or any other one using JOIN, would also ignore some possible combinations?

Yes, it's due to a bad planning of the application at the beginning. At first they could work separately and a user do a prediction for one of both choices per game, or the two. After building the DB, due to lack of time, it was decided to force a user to do both predictions at once.

Still doesn't explain the need for a second table. why not a column in the one table to differentiate predictions?

Well, you're absolutely right, and now I think how dumb I was not to think on using an only table for that when I was planning the structure, but now I'd rather keep things as they are for the moment. There are other things to do, and I could fix this in a future, because this part of the application is not going to be extended.

I really appreciate your help and for now I will use the query with the UNION clause.

Best regards!