MS Access M:N Relationship query: exclude records with more than the requested values

Question

I have a little problem when looking up records that have m:n relationships to other tables and therefore appear multiple times in link tables. I try to show it with a minimal example here:

Let's assume we have a table called "books", one called "authors" and a link table connecting both.

Some books are written by one author, others are written by multiple authors.

As we can see, book one (book_id = 1) is written by only one person (authorID = 1) while book two (book_id = 2) is written by two people (authorID 1 and 3). If we now try to get the book which is written by author 1 and author 3 we can go like this:

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

which leads to this:

I am pretty sure that my solution to this is not the best way to do it but in this case it works... anyway, when we try to get only those books returned that are written by author 1 and nobody else, we have a problem. In code, this selection looks like this:

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;

and leads to this recordset:

while it is technically true that authorID = 1 is the author of both of these books, what I really wanted to get was only book one (book_id = 1). So here is my question: How can I exclude records that have more than the queried values linked to them in this kind of m:n relationship? And how can this be done as a SQL statement in Access 2016? Thanks in advance

Answer 1

The key is learning to write queries which directly answer the questions. Start with getting books with only one author ("and nobody else"). This will require an aggregate query which counts the authors per book:

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

Now join the results to the other tables to get detail you desire/require. I personally prefer saving such intermediate queries then referencing them by name in the next query (since you can then use the Access query designer to edit them individually), but to be explicit I'll include it as a subquery:

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 

---

Really, you could also write a query which answers your other questions like getting records with "more than the queried values"...

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

and then "exclude" such records:

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

This will initially select all records in [books] due to the left join, but will subsequently exclude books with more than 1 author by keeping only records which are NOT in the [OtherAuthors] subquery. But as you see this is an overly complex way to get the same results. I only included this to show that it is possible to exclude records using other types of joins and appropriate conditions. This is a useful technique especially when there is no way of selecting the desired records directly. (Sometimes excluding records really is the best approach, but it just isn't necessary in this case with the link table.)