Good day. There are three tables:

books (id, title) users (id, first_name, last_name, age) users_books (id, user_id, book_id)

The users_books table contains information about books purchased by users. You must select a list of users who did not buy a book with a specific title. Can I do better than I did?

My request:

  SELECT DISTINCT users.first_name, users.last_name, users.id FROM users INNER JOIN users_books ON users.id = users_books.user_id WHERE users_books.user_id NOT IN (SELECT user_id FROM users_books WHERE users_books.book_id = (SELECT id FROM books WHERE title = "Book")) GROUP BY users_books.user_id
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

You can make one subquery:

 SELECT id, first_name, last_name FROM users WHERE id NOT IN (SELECT DISTINCT ub.user_id FROM books b LEFT JOIN users_books ub ON b.id = ub.book_id WHERE b.title = 'Book' AND ub.id IS NOT NULL)

In essence, the same as in the original request. The nested queries are combined with the left join => it is important to make sure that the books that no one bought at all (ub.id IS NOT NULL) were not included in the selection.

DISTINCT in a subquery in case a situation is suddenly possible when one user bought one book several times.

Outside DISTINCT is not needed.

UPD. Improved on the recommendations of Mike request:

 SELECT id, first_name, last_name FROM users WHERE id NOT IN (SELECT ub.user_id FROM books b JOIN users_books ub ON b.id = ub.book_id WHERE b.title = 'Book')
  • @Mike, thanks for the comment. Corrected the answer. And in fact, how many times used joins, and still there is room to grow. Cool. - Denis Khvorostin