1

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?

0
2

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.