0

I was working in mySQL and made a fake database for reviews, reviewers and tv series. So I made 3 different tables, one for reviewers, one for reviews and one for the series.

CREATE TABLE reviewers
(
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(150) NOT NULL
);

CREATE TABLE series
(
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
released_year YEAR(4),
genre VARCHAR(50)
);

CREATE TABLE reviews(
id INT AUTO_INCREMENT PRIMARY KEY,
rating DECIMAL(2,1),
series_id INT,
reviewer_id INT,
FOREIGN KEY(series_id) REFERENCES series(id),
FOREIGN KEY(reviewer_id) REFERENCES reviewers(id)
);

The thing that I wanted to ask is, how can I get the highest rating from each reviewer and in which show they gave it?

UPDATE

I came up with this code

SELECT first_name,last_name,title, a.series_id,a.rating FROM
( SELECT series_id,MAX(rating) AS max FROM reviews 
 GROUP BY series_id ) AS b
 INNER JOIN reviews AS a
 ON a.series_id=b.series_id AND a.rating=b.max
 INNER JOIN reviewers
 ON reviewers.id=a.reviewer_id
 INNER JOIN series
 ON series.id=a.series_id
 GROUP BY series_id;

which gives me the max rating in each series and who gave that rating

ekad
  • 14,436
  • 26
  • 44
  • 46
Dimitris Kazantzidis
  • 1
  • 3
  • Get the highest review using the technique in https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1, then join that subquery with `series` to get the name of the show. – Barmar Apr 30 '18 at 20:51
  • 1
    SO expects you to [try to solve your own problem first](http://meta.stackoverflow.com/questions/261592). Please update your question to show what you have already tried in a [mcve]. For further information, please see [ask], and take the [tour] :) – Barmar Apr 30 '18 at 21:29

0 Answers0