CODEWITHSUNDEEP
mysqlminimum
Javeria Habib
Javeria Habib

Reputation: 557

Enlist minimum values after joining tables

Here is my database:

create table Movie (
mID smallint auto_increment primary key, 
title varchar(30), 
director varchar(20),
year int
)engine innodb;

create table Reviewer (
rID smallint auto_increment primary key, 
name varchar(30)
)engine innodb;

create table Rating (
mID smallint, 
rID smallint, 
stars int, 
ratingDate date,
constraint fk_movie foreign key (mID) references movie(mID),
constraint fk_reviewer foreign key (rID) references reviewer(rID)
)engine innodb;

insert into movie (title,director,year) values ('Lincoln','Steven Spielberg',2012),('An Unexpected Journey','Peter Jackson',2012),('Jurassic Park','Steven Spielberg',1993),('The Godfather','Francis Ford Coppola',1972), ('Fight Club','David Fincher',1999),('War Horse','Steven Spielberg',2011),('Rise of guardians','Peter Ramson',2012),('The Shawshank Redemption','Frank Darabont',1994),('Jaws','Steven Spielberg',1975),('Raging Bull','Martin Scorsese',1980);

insert into reviewer (name) values ('Bill Goodykoontz'),('David Germain'),('Christy Lemire'),('Richard Lawson'),('Marjorie Baumgarten');

insert into rating (mID, rID, stars, ratingDate) values (1,4,3,'2012-04-09'),(3,5,4,'2000-03-05'),(4,2,5,'1900-09-15'),(7,3,5,'2012-08-25'),(6,1,4,'2011-09-11'),(8,5,5,'1995-10-15'),(9,3,3,'1980-02-20');

Here's SQL Fiddle with all this data inserted.

I have to: "For each rating that is the lowest (fewest stars) currently in the database, return the reviewer name, movie title and the number of stars."

My code is:

SELECT name REVIEWER, title MOVIE, stars FROM rating
INNER JOIN movie
USING(mID)
INNER JOIN reviewer
USING(rID)
GROUP BY stars
HAVING stars = min(stars);

My Output is

REVIEWER    MOVIE   stars
Christy Lemire      Jaws    3
Bill Goodykoontz    War Horse   4
David Germain       The Godfather   5

Ideal Output

REVIEWER    MOVIE   stars
Christy Lemire  Jaws    3
Richard Lawson  Lincoln 3

Someone kindly correct my code.

Upvotes: 2

Views: 167

Answers (2)

Taryn
Taryn

Reputation: 247680

You can use a WHERE clause to filter this:

SELECT v.name REVIEWER, 
  m.title MOVIE, 
  r.stars 
FROM movie m
INNER JOIN rating r
  on r.mid = m.mid
INNER JOIN reviewer v
  on r.rid = v.rid
where r.stars = (select min(stars)
                 from rating)

See SQL Fiddle with Demo

Returns the result:

|       REVIEWER |   MOVIE | STARS |
------------------------------------
| Christy Lemire |    Jaws |     3 |
| Richard Lawson | Lincoln |     3 |

Upvotes: 1

raina77ow
raina77ow

Reputation: 106385

Aren't you looking for this:

    SELECT name REVIEWER, title MOVIE, stars FROM rating
INNER JOIN movie    USING(mID)
INNER JOIN reviewer USING(rID)
WHERE stars = 
  (SELECT min(stars) FROM rating);

SQL Fiddle.

Upvotes: 2

Related Questions