-1

I need to formulate a query that gets me the row in one table based on the ID from a result of an average function in a grouped query in another table.

I have two tables, one for book details and one with their ratings ('books' and 'reviews'). Stripped down to the essentials:

+--------+--------+
| bookid |  name  |
+--------+--------+
|    4   |  LOTR  |
|    5   |  Bible |
+--------+--------+

+--------+--------+
| bookid | rating |
+--------+--------+
|    4   |   2    |
|    5   |   2    |
|    4   |   3    |
|    5   |   4    |
+--------+--------+

With a query

SELECT bookid
     , AVG(rating) AS avg_rating 
  FROM reviews 
 GROUP 
    BY book_id 
 ORDER 
    BY avg_rating DESC

I get the best average score. In this case, bookid 4 has a score of 2.5 and bookid 5 has a score of 3; the query returns one row: the bookid 5 as the first part, and the score 3 as the second.

By keeping the virtual column result in a variable and issuing a new query, I can get hold of the book name:

$topbook = $result['bookid'];
$topscore = $result['avg_rating']; 
$getname = "
SELECT name 
  FROM books 
 WHERE bookid = $topbook
";
[...]
print "Best book: $getname with a score of $topscore"; // Bible with a score of 3

This works. But I am curious as to how I can combine this into one query, joining the tables based on a condition that is the result of a virtual calculation involving grouping, as shown above? All while also keeping the second value in the first query (avg_rating) for printing?

Any pointers are much appreciated, thank you!

Strawberry
  • 33,750
  • 13
  • 40
  • 57
whatqnavry
  • 3
  • 1
  • Within your SELECT can you not just do `SELECT bookid, name, AVG(rating) AS avg_rating ...` – hppycoder Mar 17 '21 at 23:03
  • @hppycoder thanks for the quick reaction! But 'name' is a column in the books table while 'AVG(rating)' is in the reviews table; hence the "SELECT ... FROM reviews" in my first query. Could you please clarify with the complete query? as I am unsure how you would solve the FROM part. Thanks! :) – whatqnavry Mar 17 '21 at 23:11
  • Thanks for the clarification, i've submitted an answer below. It will be using LEFT JOIN which has a good description here - https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins – hppycoder Mar 17 '21 at 23:16
  • Find out how to use a "subquery" including a "scalar subquery". – philipxy Mar 17 '21 at 23:52

1 Answers1

0

Thanks for the clarification in the comments. We'll use LEFT JOIN here to combine the two tables together with the common key being bookid in reviews linking to bookid in the books table.

SELECT r.bookid, AVG(r.rating) AS avg_rating, b.name
FROM reviews r
LEFT JOIN books b ON b.bookid = r.bookid
GROUP BY r.bookid 
ORDER BY avg_rating DESC
hppycoder
  • 1,016
  • 1
  • 6
  • 13