Ms-access – Compare all attributes in M:N relationship

ms-access-2016subquery

I am new to database development and I'm sure my current problem is quite common. Unfortunately I couldn't find a solution yet – mainly because I'm not sure how to name it correctly 😉 So if there is a solution available already or there is a better way to describe it, please tell me and I'll rename this post.

My problem is that I have a database with an M:N relationship and I'm looking for a way to check if a record already exists.

Example:

Minimal example ER-model

Books can have multiple places of publication as well as authors. To avoid duplicates, the database should be checked before a new book is added.
If there is only one author and place of publication the query will return just one record:

enter image description here

But if there are for example two authors, 2 rows of the same book will be returned. To make it even more complicated, let's say there are two books with same title, year of publication and pagecount but different authors, this will be returned:

enter image description here

Thats where my problem starts: I want to check if the record with book_id "2" already exists (obviously without knowing the book_id) and if it exists what its book_id is. In this example the only way to achieve this is to check if both authors names (or IDs to make it a little bit shorter) are associated with one of the books having the destinct values in title, year and pagecount – and I don't have any idea how to do that (ideally in SQL).

So thank you in advance for your help 🙂

Best Answer

Well, after some experiments and close reading of multiple SQL tutorials, I found this is a quite good solution:

SELECT DISTINCTROW book_id, title, year_of_publication FROM
((book
INNER JOIN (SELECT * FROM link_author_book 
                 INNER JOIN author ON link_author_book.author = author.auth_id
                 WHERE author.firstname = "Jeff" AND author.lastname = "Carpenter") AS author
ON author.book = book.book_id)

INNER JOIN (SELECT * FROM link_place_book
                 INNER JOIN pub_place ON link_place_book.p_place = pub_place.place_id
                 WHERE pub_place.placename = "Sebastopol" OR pub_place.placename = "Tokyo") AS place 
ON place.book = book.book_id)

WHERE title = "Cassandra: The Definitive Guide, 2nd Edition" AND
year_of_publication = 2016 AND pagecount = 370;

This should return exactly the required book and nothing else. Obviously, hard coded values can be replaced with values from frontend forms or something, to make this more flexible ... Any suggestions to optimize this solution are welcome.

Related Question