我想在MySQL中加入五个表

Question

null

• 作者(PK)
• AuthorName

• Pubid(PK)
• pubName

• 书籍ID(FK)
• authorid

null

null

我应该只获得一行查询以获得以上结果。我写的是：

select books.*, authors.authorname
from authors,books
inner join bookauthors on books.bookid = bookauthors.bookid
inner join publisherbook on books.bookid = publisherbook.bookid
where books.bookid = 459567;

但相反，我得到了许多具有重复数据的行。

Answer 1

您需要对所有表使用联接。在from子句中使用多个表将导致笛卡尔积。试试这个

SELECT books.bookid, books.title, books.price, authors.authorname
FROM books
INNER JOIN bookauthors ON books.bookid = bookauthors.bookid
INNER JOIN publisherbook ON books.bookid = publisherbook.bookid
INNER JOIN authors ON bookauthors.authorid = authors.authorid -- assuming you meant authorid and not authored
WHERE books.bookid = 459567;

当然，如果您的书有多个作者，您将在您的ResultSet中获得多行。

我不确定您为什么加入publisherbook（或从原始列表中加入bookpublisher)，除非您只是为了确保您的书有一个出版商。同样，多个发布者将导致更多（重复的）结果。

SELECT books.bookid, books.title, books.price, authors.authorname
FROM books
INNER JOIN bookauthors ON books.bookid = bookauthors.bookid
INNER JOIN authors ON bookauthors.authorid = authors.authorid -- assuming you meant authorid and not authored
WHERE EXISTS (
    SELECT 1 FROM publisherbook
    WHERE publisherbook.bookid = books.bookid
) AND books.bookid = 459567;