多个 SQL 连接

Multiple SQL joins

提问by GGe

I need to execute a query To retrieve data from multiple tables but I'm rather confused on how to do it all at once.

我需要执行一个查询来从多个表中检索数据，但我对如何一次完成所有操作感到困惑。

Books: _ISBN , BookTitle, Edition, Year, PublisherID, Pages, Rating
Categories: _CategoryID, Category
Categories_Books: _Categories_Category_ID, _Books_ISBN
Publishers: _Publisherid, Publisher
Writers: _WriterID, LastName
Writers_Books: _Writers_WriterID, _Books_ISBN

Categories_Booksand Writers_Booksare the intermediate tables to help me implement many to many relationships between the tables.

Categories_Books并且Writers_Books是帮助我在表之间实现多对多关系的中间表。

I need a single query with multiple joins to select:

我需要一个带有多个连接的查询来选择：

• Title, Edition, Year, Pages, Rating from Books
• Category from Categories
• Publisher from Publishers
• LastName from Writers

• 书名、版本、年份、页数、书籍评级
• 类别中的类别
• 来自出版商的出版商
• 作家的姓氏

回答by Josh M.

It will be something like this:

它会是这样的：

SELECT b.Title, b.Edition, b.Year, b.Pages, b.Rating, c.Category, p.Publisher, w.LastName
FROM
    Books b
    JOIN Categories_Book cb ON cb._ISBN = b._Books_ISBN
    JOIN Category c ON c._CategoryID = cb._Categories_Category_ID
    JOIN Publishers p ON p._PublisherID = b.PublisherID
    JOIN Writers_Books wb ON wb._Books_ISBN = b._ISBN
    JOIN Writer w ON w._WritersID = wb._Writers_WriterID

You use the joinstatement to indicate which fields from table A map to table B. I'm using aliases here thats why you see Books bthe Bookstable will be referred to as bin the rest of the query. This makes for less typing.

您使用该join语句来指示表 A 中的哪些字段映射到表 B。我在这里使用别名，这就是为什么您看到Books b该Books表将b在查询的其余部分中引用的原因。这使得打字更少。

FYI your naming convention is very strange, I would expect it to be more like this:

仅供参考，您的命名约定很奇怪，我希望它更像这样：

Book: ID, ISBN , BookTitle, Edition, Year, PublisherID, Pages, Rating
Category: ID, [Name]
BookCategory: ID, CategoryID, BookID
Publisher: ID, [Name]
Writer: ID, LastName
BookWriter: ID, WriterID, BookID

回答by krtek

You can use something like this :

你可以使用这样的东西：

SELECT
    Books.BookTitle,
    Books.Edition,
    Books.Year,
    Books.Pages,
    Books.Rating,
    Categories.Category,
    Publishers.Publisher,
    Writers.LastName
FROM Books
INNER JOIN Categories_Books ON Categories_Books._Books_ISBN = Books._ISBN
INNER JOIN Categories ON Categories._CategoryID = Categories_Books._Categories_Category_ID
INNER JOIN Publishers ON Publishers._Publisherid = Books.PublisherID
INNER JOIN Writers_Books ON Writers_Books._Books_ISBN = Books._ISBN
INNER JOIN Writers ON Writers.Writers_Books = _Writers_WriterID.

回答by Yonas

 SELECT
 B.Title, B.Edition, B.Year, B.Pages, B.Rating     --from Books
, C.Category                                        --from Categories
, P.Publisher                                       --from Publishers
, W.LastName                                        --from Writers

FROM Books B

JOIN Categories_Books CB ON B._ISBN = CB._Books_ISBN
JOIN Categories_Books CB ON CB.__Categories_Category_ID = C._CategoryID
JOIN Publishers P ON B.PublisherID = P._Publisherid
JOIN Writers_Books WB ON B._ISBN = WB._Books_ISBN
JOIN Writers W ON WB._Writers_WriterID = W._WriterID