SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Thread: JOIN questions

  1. #1
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question JOIN questions

    Assume that I have two tables: "item" (where items are stores) and "rating" (where their ratings are stored).

    - Item has 'id' and 'description' for example
    - Rating has 'id', 'item_id' (FK), 'score'

    And I need to show the average score and the number of votes for all the items I have.

    I've used a basic inner join query, but then I encountered two issues:

    1) Items that don't have a score don't show up, since I join on 'rating.item_id = item.id'

    2) I can't get the average score and the number of votes for every item (or show it as 0 if it doesn't have any). When I try to use COUNT() or AVG() within the query, MySQL tells me that I need to use GROUP (or a few other clauses), otherwise it won't run it.

    Any idea?

    Appreciate your help

  2. #2
    Non-Member
    Join Date
    Nov 2008
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You probably need sub query to perform the calculation. It's good if you can create 2 fields in your item table: total_vote and average_rating

    Then every time there's a new review, you recalculate the rating and update item table.

    By doing this, you can show the result quickly instead of joining tables in real time which will slow down your performance.

  3. #3
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can take care of that in PHP via caching. For now, I need to get those initial results via MySQL, which I'm not really good at.

    I'm afraid the method you've described adds more things than actually needed (correct me if I'm wrong).

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you don't actually need a subquery to do the calculation, but i use it often to avoid putting the GROUP BY in the outer query
    Code:
    SELECT i.id
         , i.description
         , r.avg_score
         , r.votes
      FROM item AS i
    LEFT OUTER
      JOIN ( SELECT item_id
                  , AVG(score) AS avg_score
                  , COUNT(*) AS votes
               FROM rating
             GROUP
                 BY item_id ) AS r
        ON r.item_id = i.id
    if an item has no votes yet, the last two columns in the result row will by NULL, but you can use COALESCE on them if you want to see zeroes instead

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy.

    Here's what I'm doing, getting items for a specific user (that has an id of 1) along with the number of votes and the average score:

    Code MySQL:
    SELECT item.*
    FROM item
    WHERE item.user_id = 1
    LEFT OUTER JOIN (
    SELECT item_id, AVG( score) AS rating, COUNT( * ) AS votes
    FROM rating
    GROUP BY item_id
    ) ON item.id = rating.item_id

    But then I get back a syntax error for the following lines:
    Code MySQL:
    LEFT OUTER JOIN (
    SELECT item_id, AVG( score) AS rating, COUNT( * ) AS votes

    What am I doing wrong here?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the LEFT OUTER JOIN is part of the FROM clause

    the WHERE clause comes after the FROM clause



    plus, you switched to the dreaded, evil "select star" and i think you will find that this is inadequate

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It worked!! I was also able to add more joins to pull relevant information from different tables, which is EXACTLY what I wanted. Thanks.

    Just one more question so I can fully understand the solution: Why do we have to select columns from the joined tables in the query? shouldn't the ones in the sub-queries technically suffice?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the subquery in the FROM clause is a table -- it's called a derived table or sometimes an inline view

    you need to put the columns that you want returned into the SELECT clause just like with any other table in the FROM clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it! Thanks Rudy. I really appreciate your help

  10. #10
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I just tried to simply join an additional table to the query:
    Code MySQL:
    SELECT i.id
         , i.description
         , r.avg_score
         , r.votes
      FROM item AS i
     
    LEFT JOIN table2 ON table2.item_id = i.id
     
    LEFT OUTER
      JOIN ( SELECT item_id
                  , AVG(score) AS avg_score
                  , COUNT(*) AS votes
               FROM rating
             GROUP
                 BY item_id ) AS r
        ON r.item_id = i.id

    And it works, but I get duplicate results. Every record appears twice.

    Why is this happening? and how do I fix it?

    Appreciate your help.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how do you fix it? that depends on what table2 is for

    you get one row per item from the item table (by definition)

    you also get one row per item from the derived table (by virtue of the GROUP BY)

    therefore if you are getting dupes, the only reason it could possibly be is that there is more than one row per item in table2

    simple, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard mPeror's Avatar
    Join Date
    Mar 2005
    Location
    Saudi Arabia
    Posts
    1,724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ugh! I just found out that the other developer modified the other table yesterday so it contains multiple records for the same item. I guess I've been trying to fix the wrong part of the code the whole time.

    Thanks Rudy.


Bookmarks

Posting Permissions

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