Creating a table that will give me a list of books with and without reviews for the ones that do

Question

I have the following code used to create the database of a book store:

create database Bookorama

use Bookorama

--Book table
create table Book
( 
    bookID int identity(1,1),
    bookName varchar(50) unique,
    price smallmoney,

    primary key (bookID)
);

--Customer table
create table Customer
(
    custID int not null identity (1,1),
    cName varchar(50),
    cemid varchar(50),

    primary key (custID)
);

-- Order table
create table Ordertable
(
    orderID int identity(1,1),
    custID int not null,
    bookID int not null,

    primary key (orderID),
    foreign key (custid) references Customer(custid),
    foreign key (bookID) references Book(bookid)
);

create table Reviews
(
    reviewID int identity (1,1) not null,
    custID int not null,
    bookID int not null,
    review varchar(50),

    primary key (reviewID),
    foreign key (custID) references Customer(custID),
    foreign key (bookID) references Book(bookID)
)

For my assignment, I have been given the following task:

Write a query that will display a list of all books, and for those that have a review, list the review.

I'm utterly confused and have no clue as to how I would start to approach this. I'm unaware of any JOIN combinations I could use. I am rather new to SQL. What trips me up is that I would have to access both the the tables to retrieve both the book name using the book id and then create an association with its corresponding review. All while also listing books that do not have reviews either.

Answer 1

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

For example on MSSQL:

USE AdventureWorks2008R2;
GO
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID

Answer 2

SELECT *
FROM Books
LEFT JOIN Book.bookID ON Reviews.bookID
WHERE Reviews.review IS NOT NULL

As per Sean Lange, simplifying the query by skipping WHERE clause by using INNER JOIN:

SELECT *
FROM Books
INNER JOIN Book.bookID ON Reviews.bookID

Answer 3

Just select the bookName and the reviews. This will list all books and its reviews and NULL if there aren't any.

SELECT bookName,
       review
  FROM book
  LEFT JOIN reviews
    ON book.bookID = reviews.bookID

Answer 4

Write a query that will display a list of all books, and for those that have a review, list the review.

Your quoted problem statement is not clear. First, it intends but does not express something like, "display the list of (bookID, reviewID) rows where ...". Second, it intends but does not express something like "... where the book has that review or the book doesn't have a review and the reviewID is ..." what? You are expected to know/guess/decide to finish this with ... "NULL"? The empty string? Who knows, it's not clear.

Or maybe they want "(bookName, review) rows where some book with that name either has some review where the text is (column) review or doesn't have a review and (column) review is ...". (Note that's "some", not "the".) Who knows, it's not clear.

(This sort of sloppiness permeates database pedagogy & culture.)

What trips me up is that I would have to access both the the tables to retrieve both the book name using the book id, and then create an association with its corresponding review.

That is what JOINs do.

A table holds rows whose values are related/associated in some way. We can describe a relation(ship)/association in terms of a predicate--a column-parameterized sentence template:

/*  the rows where
    book bookID has title bookName and costs $price
*/
select * from Book

Inside a FROM, each argument table column is renamed to be prefixed by its table name/alias and a dot. Then INNER JOIN generates every combination of a row from each. When there are no duplicates, INNER JOIN holds the rows that are associated per the conjunction/AND of argument associations. ON and WHERE both modify the FROM result to return rows associated by conjunction/AND of their condition.

...
FROM Book b JOIN Reviews r ON b.bookID = r.bookID
/*  the rows where
        book b.bookID has title b.bookName and costs $b.price
    AND review r.reviewID is by customer r.custID of book r.bookID with text r.review
    AND b.bookID = r.bookID
*/

SELECT adds & drops columns while dropping dot prefixes. When we don't want duplicates but might get some, we use DISTINCT.

/*  the rows where
    FOR SOME values for b.*, r.*,
        bookID = b.bookID AND reviewID = r.reviewID
    AND book b.bookID has title b.bookName and costs $b.price
    AND review r.reviewID is by customer r.custID of book r.bookID with text r.review
    AND b.bookID = r.bookID
*/
SELECT b.bookID, r.reviewID
FROM Book b JOIN Reviews r ON b.bookID = r.bookID

It happens that we frequently want the rows from INNER JOIN ON plus unmatched left table rows extended by NULL for right table columns. That's LEFT JOIN ON.

...
FROM Book b LEFT JOIN Reviews r ON b.bookID = r.bookID
/*  the rows where
            book b.bookID has title b.bookName and costs $b.price
        AND review r.reviewID is by customer r.custID of book r.bookID with text r.review
        AND b.bookID = r.bookID
    OR      book b.bookID has title b.bookName and costs $b.price
        AND NOT EXISTS r.reviewID, r.custID, r.book, r.review [
                review r.reviewID is by customer r.custID of book r.bookID with text r.review
                AND b.bookID = r.bookID
            ]
        AND r.reviewID IS NULL AND r.custID IS NULL AND r.book IS NULL AND r.review IS NULL
/*

So you want:

SELECT b.bookID, r.reviewID
FROM Book b LEFT JOIN Reviews r ON b.bookID = r.reviewID

or maybe:

SELECT DISTINCT b.bookName, r.review
FROM Book b LEFT JOIN Reviews r ON b.bookID = r.reviewID

CROSS JOIN vs INNER JOIN in SQL Server 2008
Is there any rule of thumb to construct SQL query from a human-readable description?

When there are duplicates or NULLs, querying is complicated, because their presence becomes more and more obscurely related to a given situation in terms of AND, OR, NOT, FOR SOME, etc. (It's straightforward to generate predicates, but what they say is complicated.)