Mysql Selecting the average value of a column from things that are stored in 3 tables-CodePudding

I am fairly new with databases and I am starting with mysql.

I have 4 tables (movie, genre, movieGenre and movieRating):

movie:

CREATE TABLE `movie` (
  `movieId` INT NOT NULL,
  `title` VARCHAR(155) NOT NULL,
  PRIMARY KEY  (`movieId`)
);

genre

CREATE TABLE `genre` (
  `code` INT NOT NULL AUTO_INCREMENT,
  `genre` VARCHAR(20) NOT NULL,
  PRIMARY KEY  (`code`)
);

movieGenre

CREATE TABLE `movieGenre` (
  `movieId` INT,
  `genreId` INT,
  CONSTRAINT `fk_movieGenre_movie` FOREIGN KEY (`movieId`) REFERENCES `movie`(`movieId`),
  CONSTRAINT `fk_movieGenre_genre` FOREIGN KEY (`genreId`) references `genre`(`code`)
);

and movieRating

CREATE TABLE `movieRating` (
  `userId` INT NOT NULL AUTO_INCREMENT,
  `movieId` INT NOT NULL,
  `rating` FLOAT NOT NULL,
  `date` DATE,
  CONSTRAINT `fk_movieRating_user` FOREIGN KEY (`userId`) REFERENCES `user`(`userId`),
  CONSTRAINT `fk_movieRating_movie` FOREIGN KEY (`movieId`) REFERENCES `movie`(`movieId`)
);

I need to find the average rate for each movie genre, sorted in descended average rating value and if a genre does not have any associated rating, it should be reported with 0 ratings value

I am lost. I don't know how to achieve this result. Could you please help me?

I have figured out how to find the avg rate for each movie but I don't know how to change this so I find for each genre:

SELECT `movie`.`movieId`, AVG(`movieRating`.`rating`) FROM `movie`
INNER JOIN `movieRating` ON `movie`.`movieId` = `movieRating`.`movieId`
GROUP BY `movieRating`.`movieId`
ORDER BY AVG(`movieRating`.`rating`) DESC;

CodePudding user response：

Well, I have put an ID in your genre table, otherwise I can't make this work. So, it has become:

CREATE TABLE `genre` (
  `genreId` INT,
  `code` INT NOT NULL AUTO_INCREMENT,
  `genre` VARCHAR(20) NOT NULL,
  PRIMARY KEY  (`code`)
);

And I have to make the assumption that all the genres are defined in this table. I'll take this table as a base, and, as you suggested use a subquery:

SELECT
  genre.code,
  genre.genre,
  (<my sub select comes here>)
FROM
  genre;

This basically gets you a list of all genres. Now it is up to the subquery to give the average rate for the movies in each genre. That subquery could look something like this:

SELECT AVG(movieRating.rating)
FROM movieRating
JOIN movie ON movie.movieId = movieRating.movieId  
JOIN movieGenre ON movieGenre.movieId = M.movieId  
WHERE movieGenre.genreId = genre.genreId;

I kept it very simple. We start with the average we want, from movieRating, and work through the movie and movieGenre tables to get to the genreId in that last table. Notice the genre.genreId which comes from the main query. We are implicitly grouping by genreId.

Now you can put this subselect in the main query, but that still doesn't solve the situation in which there is not rating to take an average from. It would result in NULL, meaning: no result. That is almost good enough, but you could put a IFNULL() around it to get a proper zero result.

The total query would then become this:

SELECT
  genre.code,
  genre.genre,
  IFNULL((SELECT AVG(movieRating.rating)
          FROM movieRating
          JOIN movie ON movie.movieId = movieRating.movieId  
          JOIN movieGenre ON movieGenre.movieId = M.movieId  
          WHERE movieGenre.genreId = genre.genreId), 0) AS Average
FROM
  genre;

I can't guarantee this will work since I cannot test it, and testing is everything when writing queries.

CodePudding user response：

You should left join all tables and then group by gerne

SELECT `genre`,AVG(IFNULL(`rating`,0)) avgrate
FROM `movie` m
LEFT JOIN `movieRating` mr ON m.`movieId` = mr.`movieId`
LEFT JOIN movieGenre mg ON mg.`movieId` = m.`movieId` 
LEFT JOIN `genre` g ON g.`code` = mg.`genreId`
GROUP BY `genre`

I general produce data for your tables, and then start by joing the tables, and see f you get the result you want, if not change the joins to LET Join one by one till you get the result you want, of course you need ro calculate teh avg from 3 or 4 movies