如何在多对多场景中查询没有作者的书籍

I have the following database design in relating books and authors:

我在相关书籍和作者方面有以下数据库设计:

Books(bookid,title) Authors(authorid,authorname) BooksAuthors(bookid,authorid)

书籍(bookid,title)作者(authorid,authorname)BooksAuthors(bookid,authorid)

I have no problem querying records with authors but I could not pull out records with no authors. I base such assumption that there are books with no authors. Here is my query by the way:

我与作者查询记录没有问题,但我无法提取没有作者的记录。我的基础是假设有没有作者的书。这是我的查询方式:

SELECT DISTINCT Books.title, 
                GROUP_CONCAT(DISTINCT Authors.authorname SEPARATOR ', ') AS authors 
FROM Books 
INNER JOIN BooksAuthors USING (bookid) 
INNER JOIN Authors USING (authorid) GROUP BY  books.bookid

2 个解决方案

#1

SELECT bookid, title FROM Books INNER JOIN (
    SELECT bookid FROM BooksAuthors WHERE authorid IS NULL) AS T1
ON Books.bookid = T1.bookid;

This will basically do the trick.

这基本上可以解决问题。

#2

try yo use LEFT JOIN, and maybe you will need the extra adding HAVING part ... try without and with and see what you get

尝试你使用LEFT JOIN,也许你需要额外添加HAVING部分...尝试没有和使用,看看你得到了什么

SELECT DISTINCT Books.title, 
            GROUP_CONCAT(DISTINCT Authors.authorname SEPARATOR ', ') AS authors 
FROM Books 
INNER JOIN BooksAuthors ON BooksAuthors.bookid = Books.bookid
LEFT JOIN Authors ON Authors.authorid = BooksAuthors.authorid
GROUP BY books.bookid
HAVING Authors.authorid IS NOT NULL

#1

SELECT bookid, title FROM Books INNER JOIN (
    SELECT bookid FROM BooksAuthors WHERE authorid IS NULL) AS T1
ON Books.bookid = T1.bookid;