Unable to figure out a SQL query involving 4 tables in MySQL

2 comments

1. Ok, definitely not my prettiest work, especially since I don’t usually work in MySQL (EDIT: SQLFiddle is back up. Fixed an inner group, now this works):

   SELECT topItemsAllUsers.* FROM
     (SELECT 
       u.id AS userId, 
       u.name, 
       topItems.itemId
     FROM
       (SELECT 
         iwa.id AS itemId
       FROM
         (SELECT 
           MAX(AverageRating) AS MaxRating
         FROM
           (SELECT 
             i.id, 
             AVG(COALESCE(r.rating, 0)) AS AverageRating
           FROM Item i
           LEFT JOIN Rates r ON r.item = i.id
           GROUP BY i.id
           ) AS averages
         ) AS MaxOuterRating
         INNER JOIN 
         (SELECT 
           i.id, 
           AVG(COALESCE(r.rating, 0)) AS AverageRating
         FROM Item i
         LEFT JOIN Rates r ON r.item = i.id
         GROUP BY i.id
         ) as iwa ON iwa.AverageRating = MaxOuterRating.MaxRating
       ) as topItems
     CROSS JOIN
     User u
     ) as topItemsAllUsers
   LEFT JOIN Buys b ON topItemsAllUsers.userId = b.user AND topItemsAllUsers.itemId = b.item
   WHERE b.user IS NULL
   

   Here is the SQLFiddle

   In TSQL, I would at least use a CTE for that table of average ratings. This was a lot tougher than it looked initially!

   EDIT: Some explanation follows. The first thing to get is each item’s average rating, using 0 for items with no rating (hence the COALESCE() statement):

   (SELECT 
     i.id, 
     AVG(COALESCE(r.rating, 0)) AS AverageRating
   FROM Item i
   LEFT JOIN Rates r ON r.item = i.id
   GROUP BY i.id)
   

   This will list each item id once with its average rating. I named this averages, and I actually used it query twice (naming it iwa the second time. I don’t remember what “iwa” was supposed to mean any more…), once to get the actual highest rating:

   SELECT
     MAX(AverageRating) AS MaxRating
   FROM averages
   

   and named that MaxOuterRating, then INNER JOINed that result back onto iwa, on AverageRating = MaxRating , to only get item(s) with that highest rating:

   SELECT
     iwa.itemId
   FROM
   MaxOuterRating
   INNER JOIN iwa ON iwa.AverageRating = MaxOuterRating.MaxRating
   

   This result is contained in the topItems alias.

   Now that we have only the item(s) with the top rating, CROSS JOIN with User to get a table with every top item for every user:

   SELECT 
     ... 
   FROM
   topItems
   CROSS JOIN
   Users
   

   This result is in topItemsAllUsers.

   Finally, do LEFT JOIN with Buys on both user id and item id, then restrict results to only those rows where there are no Buys records associated (This is usually called an exclude join):

   SELECT 
     topItemsAllUsers.*
   FROM
   topItemsAllUsers
   LEFT JOIN Buys b ON topItemsAllUsers.userId = b.user AND topItemsAllUsers.itemId = b.item
   WHERE b.user IS NULL
   

   Et viola. None of the operations is particularly difficult, but they are nested up so badly, it was difficult to see how to attack. I do not doubt this could be improved greatly, but this does return expected results.

   Log in to Reply

2. So just for starters, the list of items not bought by respective users is as follows, right?

       SELECT u.*
            , i.* 
         FROM user u 
         JOIN item i 
         LEFT 
         JOIN buys b 
           ON b.user = u.id 
          AND b.item = i.id 
        WHERE b.item IS NULL;
   

   … in which case …

   SELECT x.* FROM
   (
   SELECT u.id user_id
        , u.name
        , i.id item_id
        , i.description
        , i.category
        , i.price
        , r.rating
     FROM user u 
     JOIN item i 
     LEFT 
     JOIN buys b 
       ON b.user = u.id AND b.item = i.id
     JOIN rates r
       ON r.item = i.id
    WHERE b.item IS NULL
   ) x
   JOIN
   (
   SELECT u.id,r.rating 
     FROM user u 
     JOIN item i 
     LEFT 
     JOIN buys b 
       ON b.user = u.id AND b.item = i.id
     JOIN rates r
       ON r.item = i.id
     JOIN (SELECT AVG(rating) max_avg FROM rates GROUP BY item ORDER BY AVG(rating) DESC LIMIT 1) n
       ON n.max_avg = r.rating
    WHERE b.item IS NULL
    GROUP 
       BY u.id
    ) y
    ON y.id = x.user_id
   AND y.rating = x.rating
   ORDER 
     BY user_id,item_id;
   

   … should produce the desired result

   Edited to incorporate Paul Griffin’s observation, although in doing so, I’ve perhaps made the query more convoluted than it needs to be.

   Log in to Reply