如何通过多个参数来扩展查询

Question

我必须列出作者和他们的书。这个查询看起来正确吗？因为它为每个作者列出了相同的书，我不知道是数据库问题还是我的查询。

此外，我如何扩展此查询以显示每本书的类别？

author具有name, surname, author_id

book具有title, book_id, category_id

book_author具有id, author_id, book_id

category具有category_id, name

SELECT a.name, a.surname, b.title
FROM author a, book b, book_author c
WHERE c.author_id =  a.author_id

Answer 1

您没有为book表包含联接条件，因此每本书都与每一个author行相匹配。你可以通过添加另一个条件来解决这个问题：

SELECT a.name, a.surname, b.title
FROM author a, book b, book_author c
WHERE c.author_id = a.author_id
AND c.book_id = b.book_id

但正如Stu所说，使用正确的连接语法会使这一点更加清晰：

SELECT a.name, a.surname, b.title
FROM author a
JOIN book_author ba ON (a.author_id = ba.author_id)
JOIN book b ON (b.book_id = ba.book_id)

在这一点上，应该很清楚如何将该类别也包括在内：

SELECT a.name, a.surname, b.title, c.name as category_name
FROM author a
JOIN book_author ba ON (a.author_id = ba.author_id)
JOIN book b ON (b.book_id = ba.book_id)
JOIN category c ON (c.category_id = b.category_id)