MySQL Query Help

Question

I have the following 5 tables:

users(user_id)
books(book_id, author_id)
source_phrases(source_phrase_id, book_id, phrase)
synonym_phrases(synonym_phrase_id, source_phrase_id, reader_id, synonym)
synonym_ratings(synonym_ratings_id, synonym_phrase_id, rater_id, rating)

I am trying to get a query that will select all the books a user has completed reviewing.

A user will have completed reviewing a book if they have done the following for each source phrase:

User has suggested a synonym for the source phrase (reader_id in synonym_phrases table is the users id)

OR

The user has rated a synonym for the source phrase (rater_id in synonym_ratings table is the users id)

I have spent a whole day trying to get this with no joy. Any help would be much appreciated.

Answer 1

SELECT  b.*
FROM    books b
WHERE   book_id NOT IN
        (
        SELECT  sp.book_id
        FROM    source_phrases sp
        WHERE   source_phrase_id NOT IN
                (
                SELECT  syp.source_phrase_id
                FROM    synonym_phrases syp
                WHERE   reader_id = @user_id
                )
                AND source_phrase_id NOT IN
                (
                SELECT  syp.source_phrase_id
                FROM    synonym_phrases syp
                JOIN    synonym_ratings sr
                ON      sr.synonym_phrase_id = syp.synonym_phrase_id
                        AND sr.rater_id = @user_id
                )
        )
        AND book_id IN
        (
        SELECT  sp.book_id
        FROM    source_phrases sp
        )