Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2010
    Posts
    5

    Unanswered: Struggling with a query. Please help!

    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:

    Code:
    userid   gameid      status      points
    -----------------------------------------------
    1          1             1         20
    1          2             1         24
    1          3             1         15
    2          1             1         18
    2          2             1         22
    2          3             1         16
    3          1             1         12
    3          2             1         8
    3          3             1         17
    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by luismartin View Post
    ... but the totals are much bigger than expected
    a sure sign of cross join effects

    the solution is not to join the tables
    Code:
    SELECT userid
         , p_score + r_score AS total
      FROM ( SELECT userid
                  , SUM(points) AS p_score
                  , 0           AS r_score
               FROM prediction_players 
             GROUP 
                 BY userid
             UNION ALL
             SELECT userid
                  , 0           AS p_score
                  , SUM(points) AS r_score
               FROM prediction_results 
             GROUP 
                 BY userid
           ) AS both
    ORDER 
        BY total DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    5
    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:

    Code:
    SELECT p.userid, SUM( p.points ) + (
              SELECT SUM( r.points )
              FROM prediction_results r
              WHERE p.userid = r.userid AND r.status=1 
              GROUP BY r.userid ) AS total
    FROM prediction_players p
    WHERE a.status=1 
    GROUP BY a.userid
    ORDER BY total DESC
    What do you think about this one?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by luismartin View Post
    I've found this other solution but I'm not sure whether it would get the same result or not
    that should be pretty easy to find out by testing


    Quote Originally Posted by luismartin View Post
    What do you think about this one?
    it will ignore the prediction_results score for any user who has no prediction_players score
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2010
    Posts
    5
    Quote Originally Posted by r937 View Post
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by luismartin View Post
    I suppose I would have to do a LEFT JOIN...
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2010
    Posts
    5
    Quote Originally Posted by r937 View Post
    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)
    So the previous query, or any other one using JOIN, would also ignore some possible combinations?

    Quote Originally Posted by r937 View Post
    no
    the real wtf is why there are separate tables for the predictions
    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.

  8. #8
    Join Date
    Mar 2004
    Posts
    480
    Still doesn't explain the need for a second table. why not a column in the one table to differentiate predictions?

  9. #9
    Join Date
    Sep 2010
    Posts
    5
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •