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?