Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Location
    NJ
    Posts
    14
    Rep Power
    0

    Sort result by a different tables values


    I'm working on a script that shows games compiled in a MySQL database. I'm trying to sort them 3 different ways: newest, most popular, and highest rated.

    The first two are easy because they are in the same table as the game information. However, the ratings are stored in a different table. I think this is possible to do using a join, but I don't know how to do that. The tables are set up as follows:

    table `info`
    | id | user_id | name | desc | category | plays | date_added |

    table `rating`
    | game_id | user_id | rating_value |

    Is it possible to add up the rating values, then divide that by the number of ratings for the specific id, then sort the result by the highest rated game?
  2. #2
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,142
    Rep Power
    1321
    show us what you have tried.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Location
    NJ
    Posts
    14
    Rep Power
    0
    As I stated, I don't know how to use the join function of SQL. I don't even know if this is possible to do using a join.

    What I'm trying to accomplish is quite possible to do using PHP to sort the results. However, I was wondering if there is a way to do this strictly using SQL.

    Any help is much appreciated!
  6. #4
  7. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,142
    Rep Power
    1321
    Okay so you don't know how to use a JOIN but know that you need a JOIN.

    Joins
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Location
    NJ
    Posts
    14
    Rep Power
    0
    Again, I said that I don't even know if it is possible to do using a join.

    If you could offer some assistance I'd greatly appreciate it.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Location
    NJ
    Posts
    14
    Rep Power
    0
    Well I gave that link a shot, and I came up with this:

    SELECT `id` , `name`
    FROM `info`
    JOIN `rating` AS `rating`
    ORDER BY (
    COUNT( rating.id ) / SUM( rating.rating_value )
    ) DESC

    But MySQL is giving me this error: #1052 - Column 'id' in field list is ambiguous

    I'm not sure what that means. Please help!
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by p3rk5
    I'm not sure what that means.
    it is caused by the fact that you have mentioned two tables in your FROM clause, and both of these tables have a column called id, and you reference a column called id in your SELECT clause, without qualifying which table it belongs to...

    ... so that's why it was ambiguous

    helps?



    p.s. you're going to want an ON clause for your join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Location
    NJ
    Posts
    14
    Rep Power
    0
    Yes, thank you very much. I've gotten to this point so far but it's not sorting them correctly:

    SELECT `id` , `name`
    FROM `info`
    LEFT JOIN `rating` ON rating.game_id = info.id
    GROUP BY info.id
    ORDER BY (
    COUNT( rating.game_id ) / SUM( rating.rating_value )
    ) DESC

    The games with ratings are at the top of the list, which is what I was trying to accomplish. But my calculations don't seem to be working exactly how I wanted.

    When I run SELECT COUNT( game_id ) FROM `rating`, it's returning "13" which is the number of rows in the ratings table. Is there a way to use COUNT along with DISTINCT?
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Location
    NJ
    Posts
    14
    Rep Power
    0
    Got it!

    SELECT `id` , `name`
    FROM `info`
    LEFT JOIN `rating` ON rating.game_id = info.id
    GROUP BY info.id
    ORDER BY (
    AVG( rating.rating_value )
    ) DESC

    Thanks for the help!

    Comments on this post

    • srisa agrees : For doing it yourself. We have quite a few people around here who are busy asking instead of trying.
    • pabloj agrees : +1
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by p3rk5
    Got it!
    not sure if you do

    the id column in the SELECT clause is still ambiguous

    also, the SELECT clause should probably include the average rating, don't you think? otherwise all you get is a list of ids and names that are in the right order but you don't know what the ratings are
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Location
    NJ
    Posts
    14
    Rep Power
    0
    the id column in the SELECT clause is still ambiguous
    I changed the `id` column in table `ratings` to `game_id`.

    otherwise all you get is a list of ids and names that are in the right order but you don't know what the ratings are
    In my scenario, the ratings are useless to display. I just needed to organize them that way.


    Again, thanks for the help! Is there a way I can make it so results with < 10 votes don't come up first?
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by p3rk5
    Is there a way I can make it so results with < 10 votes don't come up first?
    use a HAVING clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Location
    NJ
    Posts
    14
    Rep Power
    0
    I think my mind is stuck on PHP. How would I count the specific rows for the ID?

    My initial thoughts were to do something like this:

    SELECT `id` , `name`
    FROM `info`
    LEFT JOIN `rating` ON rating.game_id = info.id
    GROUP BY info.id
    HAVING (
    COUNT( rating.game_id ) >10
    )
    ORDER BY (
    AVG( rating.rating_value )
    ) DESC , `id`

    But wouldn't that just count all the rows in the `ratings` table? Also I got this error when running the query: #1630 - FUNCTION games.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

    I tried Googling the error but there wasn't much documentation. Any idea what's going wrong?
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    what happened to the AVG ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Location
    NJ
    Posts
    14
    Rep Power
    0

    Post


    It's still there, under the ORDER BY clause. I manged to get rid of the error. The results are still showing up in the same order though.

    SELECT `id` , `name`
    FROM `info`
    LEFT JOIN `rating` ON rating.game_id = info.id
    GROUP BY info.id
    HAVING (
    (
    SELECT COUNT( `game_id` )
    FROM `rating`
    ) >10

    Another way I was thinking to do it, if this way doesn't work, was to get the sum of `rating_values` in the HAVING clause and have it select `game_id`'s with a sum of more than 25 or so.

    One thing I'm really stuck on is how to count all the values for the specific ID. For example in PHP I could do it like this:
    PHP Code:
    $id=array('1','5','14','3','10','1');
    $total array_count_values($id);
    print_r($total); 
    Do you understand? Thanks a lot for your help so far it's really appreciated.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo