如何通过SQL跨3层外键关联检索数据：从书籍到客户列表

Alright, let's tackle this query problem using your table structure. I'll split it into two clear parts to make it easy to follow: getting the book details for a specific book_id, and then pulling the list of customers who purchased that book.

获取指定book_id的书籍信息 #

If you want the full details including edition-specific data (like publisher, edition number, etc.), you'll need to join the Books and Editions tables since they're linked via book_id:

SELECT 
    b.book_id,
    b.title,
    b.author_id,
    b.subject_id,
    e.isbn,
    e.edition,
    e.publister_id,
    e.publication,
    e.type
FROM Books b
INNER JOIN Editions e ON b.book_id = e.book_id
WHERE b.book_id = 123; -- Replace 123 with your target book_id

If you only need the basic book info (title, author_id, etc.) from the Books table alone, you can keep it simple:

SELECT * FROM Books WHERE book_id = 123; -- Replace 123 with your target book_id

反向查询关联的客户列表 #

To find all customers who've purchased the book with your specified book_id, you'll need to traverse the full chain of relationships: Books → Editions → Shipments → Customers. Here's the query:

SELECT DISTINCT
    c.c_id,
    c.First_name,
    c.Last_name
FROM Books b
INNER JOIN Editions e ON b.book_id = e.book_id
INNER JOIN Shipments s ON e.isbn = s.isbn
INNER JOIN Customers c ON s.c_id = c.c_id
WHERE b.book_id = 123; -- Replace 123 with your target book_id

A quick breakdown of why this works:

• We link Books to Editions using book_id to get all ISBNs associated with the book
• Then link Editions to Shipments using isbn to find all shipments of those ISBNs
• Finally link Shipments to Customers using c_id to get the customers who placed those orders
• The DISTINCT keyword ensures we don't get duplicate customer entries if someone bought the book multiple times

Quick Notes #

• If you want to include shipment details (like ship_date) in your results, just add s.ship_date to the SELECT clause
• If you need to account for editions that might not have any shipments yet, swap INNER JOIN with LEFT JOIN — but this will return NULLs for customer fields where there are no shipments. For most cases where you want actual customers who purchased the book, INNER JOIN is the right choice.

内容的提问来源于stack exchange，提问作者Dreeww