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
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