I have a BOOKS table, a USERS table, and a RATINGS table (with a book id, a user id, and a rating value).
For each book, I would like to be able to get an array with all the users and their rating for the chosen book BUT more importantly, if the user has not rated the book, I still want it in the array but with a rating value of null, as follows :
| user | book | value |
|---|---|---|
| 1 | 1 | 3 |
| 1 | 2 | null |
| 2 | 1 | null |
| 2 | 2 | 1 |
And so on..
The closest way I could get to it was by doing :
SELECT u.id as user, b.id as book, r.value
FROM users u, books b
LEFT OUTER JOIN ratings as r ON r.book_id = b.id
ORDER BY b.id ASC
But if I try to add LEFT OUTER JOIN ratings as r ON r.book_id = b.id AND ON r.user_id = u.id as my final clause, it obviously doesn’t work. Wrong result: it gives me the first rating found for each book, not the one related to the user.
Any idea how I could achieve this?
1 Answer 1
Try to use explicit join. It seems that MySQL has some issue referencing the implicit cross join.
select b.id, u.id, rating
from books as b
join
users as u on 1=1
left join ratings on u.id = userid and b.id = bookid