MS Access M:N 關係查詢：排除超過請求值的記錄

在查找與其他表具有 m:n 關係並因此在連結表中多次出現的記錄時，我遇到了一個小問題。我嘗試在這裡用一個最小的例子來展示它：

假設我們有一個名為“books”的表，一個名為“authors”的表和一個連接兩者的連結表。

有些書是一位作者寫的，有些是多位作者寫的。

正如我們所見，第一本書 (book_id = 1) 僅由一個人 (authorID = 1) 編寫，而第二本書 (book_id = 2) 由兩個人 (authorID 1 和 3) 編寫。如果我們現在嘗試獲取作者 1 和作者 3 所寫的書，我們可以這樣：

SELECT book_id, title, year_of_publication, pagecount, cover FROM (book
INNER JOIN 
(SELECT * FROM link_author_book WHERE author = 1) AS author1 
ON author1.book = book.book_id)
INNER JOIN
(SELECT * FROM link_author_book WHERE author = 3) AS author2
ON author2.book = book.book_id

這導致了這一點：

我很確定我對此的解決方案不是最好的方法，但在這種情況下它可以工作……無論如何，當我們試圖只返回那些由作者 1 而沒有其他人寫的書時，我們有一個問題。在程式碼中，此選擇如下所示：

SELECT book_id, title, year_of_publication, pagecount, cover FROM book
INNER JOIN 
link_author_book ON link_author_book.book = book.book_id
WHERE author = 1;

並導致此記錄集：

雖然從技術上講，authorID = 1 是這兩本書的作者，但我真正想要的只是第一本書（book_id = 1）。所以這是我的問題：在這種 m:n 關係中，我怎樣才能排除具有超過查詢值的記錄？以及如何在 Access 2016 中將其作為 SQL 語句來完成？提前致謝

關鍵是學習編寫直接回答問題的查詢。從只有一位作者（“沒有其他人”）的書籍開始。這將需要一個聚合查詢來計算每本書的作者：

SELECT lab.book FROM link_author_book AS lab
GROUP BY lab.book
HAVING Count(lab.author) = 1

現在將結果連接到其他表以獲得您想要/需要的詳細資訊。我個人更喜歡保存此類中間查詢，然後在下一個查詢中按名稱引用它們（因為您可以使用 Access 查詢設計器單獨編輯它們），但為了明確起見，我將其作為子查詢包含在內：

SELECT book.title, author.lastname
 ((SELECT lab.book, first(lab.author) AS author
   FROM link_author_book AS lab
   GROUP BY lab.book
   HAVING Count(lab.author) = 1)
 AS SingleAuthor
 INNER JOIN book ON SingleAuthor.book = book.book_id)
 INNER JOIN author ON SingleAuthor.author = author.auth_id 

---

真的，您還可以編寫一個查詢來回答您的其他問題，例如獲取“超過查詢值”的記錄……

SELECT lab.book FROM link_author_book AS lab
GROUP BY lab.book
HAVING Count(lab.author) <> 1

然後“排除”此類記錄：

SELECT book.title, author.lastname
 ((book
 LEFT JOIN (SELECT lab.book FROM link_author_book AS lab
 GROUP BY lab.book
 HAVING Count(lab.author) <> 1)
 AS OtherAuthors
 ON book.book_id = OtherAuthors.book)
 INNER JOIN link_author_book AS lab ON book.book_id = lab.book)
 INNER JOIN author ON author.auth_id = lab.author
WHERE OtherAuthors.book Is NULL

這將首先選擇所有記錄

$$ books $$由於左連接，但隨後將通過僅保留不在$$ OtherAuthors $$子查詢。但正如您所見，這是獲得相同結果的一種過於復雜的方法。我只包括這個來表明可以使用其他類型的連接和適當的條件來*排除記錄。*這是一種有用的技術，尤其是在無法直接選擇所需記錄的情況下。（有時排除記錄確實是最好的方法，但在這種情況下使用連結表就沒有必要了。）

引用自：https://dba.stackexchange.com/questions/201802