John Larsson John Larsson - 4 months ago 12
SQL Question

SQLLite Where clause with inner join

I have the following three tables:

CREATE TABLE users (
id INTEGER NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (id));

CREATE TABLE books (
id INTEGER NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (id));

CREATE TABLE reviews (
id INTEGER NOT NULL,
user_id INTEGER,
book_id INTEGER,
score INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES users (id),
FOREIGN KEY(book_id) REFERENCES books (id));


I would like to generate a list of all books and if a user is logged in, also show that users review score for that book.

Case #1 (works):
If no user logged in (user.id not set), just list all books:

sqlite> SELECT * FROM books;
id|name
1|Life of Pi
2|The Hobbit
3|Catch 22


Case #2 (works):
If user 2 is logged in (user.id = 2), list all books along with his reviews (if any):

sqlite> SELECT * FROM books LEFT OUTER JOIN reviews on books.id =
reviews.book_id WHERE reviews.user_id = 2 OR reviews.user_id IS NULL;
id|name|id|user_id|book_id|score
1|Life of Pi|2|2|1|5
2|The Hobbit|3|2|2|2
3|Catch 22||||


Case #3 (fails):
If user 1 is logged in (user.id = 1), list all books along with his reviews (if any):

sqlite> SELECT * FROM books LEFT OUTER JOIN reviews on books.id =
reviews.book_id WHERE reviews.user_id = 1 OR reviews.user_id IS NULL;
id|name|id|user_id|book_id|score
1|Life of Pi|1|1|1|3
3|Catch 22||||


Problem here is that book 2 has a review but not from user 1 so that book doesn't match the WHERE-clause.

Would appreciate any suggestions on how to solve this. Either by changing schema or my queries. I am using SQLAlchemy so using ORM would be possible aswell.

Answer

You want to move the condition to the ON clause:

SELECT *
FROM books b LEFT OUTER JOIN
     reviews r
     ON b.id = r.book_id AND r.user_id = 1;
Comments