CODEWITHSUNDEEP
sql
Jeff
Jeff

Reputation: 1004

SQL Select titles of all books that have at least one rating of 4 or higher Titles must be ordered alphabetically

You are given three tables:

create table books(id int, title varchar(250), year int, author varchar(250));

create table reviewers(id int, name varchar(250));

create table ratings(reviewer_id int, book_id int, rating int, rating_date date);

Select titles of all books that have at least one rating of 4 or higher Titles must be ordered alphabetically.

What is wrong with my statement:

SELECT title FROM books WHERE id = (SELECT book_id FROM ratings WHERE rating >= 4) ORDER BY title;

Desired Results:

A Tale Of Two Cities
And Then There Were None
Dream of the Red Chamber
She: A History of Adventure

Books: sample data

id  title   year    author
101 A Tale Of Two Cities    1859    Charles Dickens
102 The Lord of the Rings   1955    J. R. R. Tolkien
103 The Hobbit  1937    NULL
104 The Little Prince   1943    Antoine de Saint-Exupéry

Reviewers: sample data

id  name
15201   Joe Martinez
53202   Alice Lewis
44203   John Smith
Ratings: sample data
reviewer_id book_id rating  rating_date
15201   101 2   2015-02-11
15201   101 4   2015-06-16
53202   103 4   NULL

Upvotes: 0

Views: 6818

Answers (5)

KrepaFR
KrepaFR

Reputation: 9

In fact you need the "DISTINCT" keyword to avoid duplicates

SELECT DISTINCT title FROM books b
JOIN ratings r ON b.id = r.book_id
WHERE r.rating >= 4 
ORDER BY b.title;

Upvotes: 0

koryakinp
koryakinp

Reputation: 4125

You need to either use JOIN:

SELECT title FROM books b
JOIN ratings r ON b.id = r.book_id
WHERE r.rating >= 4 
ORDER BY b.title;

Or an 'IN' in WHERE clause of the sub-query:

SELECT title FROM books WHERE id IN
(SELECT book_id FROM ratings WHERE rating >= 4) 
ORDER BY title;

reviewers table is irrelevant to the question.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31775

Instead of:

...WHERE id = (SELECT...

You want: 

...WHERE id IN (SELECT...

Upvotes: 0

DDeMartini
DDeMartini

Reputation: 337

Hard to say without your data, but try this:

SELECT 
    title 
FROM 
    books 
WHERE id IN 
     (SELECT DISTINCT book_id FROM ratings WHERE rating >= 4) 
ORDER BY title;

Your WHERE clause with = is expecting a single value, not a possible group of values, and your subselect could include the same book ID many times, if there is more than one record for a book with a rating.

Give that a go.

Upvotes: 0

Matt Spinks
Matt Spinks

Reputation: 6698

You need to change that = to an IN statement:

SELECT title FROM books WHERE id IN (SELECT book_id FROM ratings WHERE rating >= 4) ORDER BY title;

...because your inner SELECT could return more than one result.

Upvotes: 2

Related Questions