1

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

Improve this question
5
  • 1
    Which DBMS product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product.
    – user1822
    Nov 5, 2017 at 14:53
  • At the moment I am working on a prototype in Access 2016. When this is running I would change to postgres 9.5. PS: sry that I forgot to name the system because its foremost a logical problem to me not yet a technical on a specific system ;)
    – MightyRavendark
    Nov 5, 2017 at 15:01
  • You can always edit your post to add important details. (Note: you can, of course, simply mention the database product(s) in the body of your post but please add them as tags as well, including the versions.)
    – Andriy M
    Dec 19, 2017 at 16:39
  • I assume that to match, a book must have the same values in book itself, plus the list of authors provided must exactly match the list associated with the book. That is to say, a book w/ title X, pub year Y, and pagecount Z and authors 127 and 256 does not match a book with X, Y, Z, and authors 127, 256, and 303. That said: while this is logically sensible, is it necessary in practice? Is your Cassandra example a real one - two books with the same title, date, pagecount, and one shared author?
    – RDFozz
    Dec 19, 2017 at 17:14
  • This example is not a real one, I have chosen to use this as a minimal example for better/faster understanding of the problem. In reality I am working on a database for some type of archaeological finds (early medieval bow fibulae). The different parts of these brooches will be recorded seperatly and to avoid redundancies during data recording the above described problem occures. I just thought it would be easier to use a quite small example instead of putting the archaeological stuff here.
    – MightyRavendark
    Dec 20, 2017 at 17:43

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.