MS Access SQL - Searching from 5 tables

Question

I have a database that stores information about Ebooks and it consists of 5 tables namely,

BOOKS (book_id, book_name, book_rating)

AUTHORS (author_id, author_name)

BOOK_AND_AUTHORS (ba_id, book_id, author_id)

TAGS (tag_id, tag_name)

BOOKS_AND_TAGS (bt_id, book_id, tag_id)

Every book has one or more authors and one or more tags (to classify them). BOOKS_AND_AUTHORS and BOOKS_AND_TAGS are junction tables used to maintain many-to-many relationships between book:authors and books:tags.

Now what I want to do is to search for a particular book with sql using multiple criteria.

For example, I want to get the names and id's of book that meet the following criteria,

Has a rating of 2 or above

Should only have the tags denoted by tag id's 2 and 219; no other tags allowed.

My solution consists of the following.

SELECT DISTINCT books.book_id, books.book_name 

FROM 
    (
    (tags INNER JOIN books_and_tags ON tags.tag_id = books_and_tags.tag_id)

    INNER JOIN 

        (books INNER JOIN 

            (authors INNER JOIN  books_and_authors 
            ON authors.author_id = books_and_authors.author_id) 

        ON books.book_id = books_and_authors.book_id) 

    ON books_and_tags.book_id = books.book_id
    )

WHERE ((BOOKS.book_rating >= 2)  AND ((TAGS.tag_id) IN (2,219))) 
GROUP BY BOOKS.book_id, BOOKS.book_name
HAVING COUNT(TAGS.tag_id) = 2

Unfortunately this doesn't return what I want. Am I doing this wrong ? Any suggestions how to implement this type of searching using SQL ? Thanks.

you might simplify to only joining the 2 tables you are talking about instead of all 5 — Randy, Apr 03 '15 at 12:05
@Randy Thanks for comment. Can't do that because doing so would complicate the code (VBA). I want the above SQL to perform searched based on any criteria. For example, if I want to search for a book using author's name + 5 tags + partial book name. — chemkatku, Apr 04 '15 at 15:32

score 0 · Accepted Answer · edited May 23 '17 at 11:59

I figured a solution after some search online.

The problem was generated by the five table join and the following part of the SQL statement:

WHERE ((BOOKS.book_rating >= 2)  AND ((TAGS.tag_id) IN (2,219))) 
GROUP BY BOOKS.book_id, BOOKS.book_name
HAVING COUNT(TAGS.tag_id) = 2

After that five tables have been joined, the COUNT(TAGS.tag_id) = 2 would not be evaluated to TRUE for some expected cases. This is because in the result-set that is generated by the join operation, contains many duplicate rows for a given book. Therefore, the COUNT... part would fail for some cases. To overcome the issue the COUNT should consider only distinct tag_id's. The solution would have been something like,

HAVING COUNT (DISTINCT TAGS.tag_id) = 2

if only MS Access SQL engine would support DISTINCT inside an aggregate function. Refer this for more info: How do I count unique items in field in Access query?

Therefore, my final solution was:

SELECT DISTINCT books.book_id, books.book_name 
FROM 

    (SELECT DISTINCT tags.tag_id, books.book_id, books.book_name 
    FROM (
        (tags INNER JOIN books_and_tags ON tags.tag_id = books_and_tags.tag_id)

        INNER JOIN 

            (books INNER JOIN 

                (authors INNER JOIN  books_and_authors 
                ON authors.author_id = books_and_authors.author_id) 

            ON books.book_id = books_and_authors.book_id) 

        ON books_and_tags.book_id = books.book_id

        ) WHERE (BOOKS.book_rating >= 2) [+ all the other conditions NOT based on tags]
    )

WHERE (TAGS.tag_id) IN (2,219) 
GROUP BY BOOKS.book_id, BOOKS.book_name
HAVING COUNT(TAGS.tag_id) = 2

Note that, the original WHERE clause has been split into two.

MS Access SQL - Searching from 5 tables

1 Answers1