mysql - 使用左连接进行条件过滤

Question

我有一个 books 、 users 和一个 books_users 表。

现在，我想要一本由我(user_id = 3)和其他用户(user_id = 18)管理的书:

SELECT *
FROM books as Book
LEFT JOIN books_users as BookUser
    ON BookUser.book_id = Book.id
WHERE
    Book.user_count = 2 AND
    BookUser.user_id = 3 AND
    BookUser.user_id = 18

问题:由于左连接将书籍的结果拆分为行，因此它不会给出包含两个用户的行...

Answer 1

我相信您想要两次加入 BookUser 表来获取这两个条件并使用 DISTINCT 来获取这本书一次:

SELECT DISTINCT Book.*
FROM books as Book
LEFT JOIN books_users as BookUser1
    ON BookUser.book_id = Book.id
LEFT JOIN books_users as BookUser2
    ON BookUser.book_id = Book.id
WHERE
    Book.user_count = 2 AND
    BookUser1.user_id = 3 AND
    BookUser2.user_id = 18