书籍
- 书号(PK)
- 标题
- 价格
作者
- 创作 (PK)
- 作者姓名
发行人
- 普比德 (PK)
- 发布名称
书籍作者
- 书号 (FK)
- 作者编号
图书出版商
- 书号 (FK)
- 普比德
我想连接所有五个表并获取这些列值:
- 书ID
- 标题
- 价格
- 作者姓名
- 发布名称
我应该只得到一行查询以获得上面的结果。我写的是:
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;
但相反,我得到了许多带有重复数据的行。
您需要对所有表使用连接。在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;
当然,如果您的图书有多个作者,您将在结果集中获得多行。
我不确定你为什么要加入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;