0

I have created a select statement that show all the topics that are assigned to a book. Now I am trying to create a Select statement that shows all the topics that are not assigned to the book. Book and topic have a many to many relationship.

Problem: I am getting the topics that are assigned to other books because technically they are not assigned to my book.

In depth look: I have a database full of books. A book can have many topics and a topic can have many books. I have create a user interface for adding a book. This interface shows all the topics available when the user choose let us say three topics three records are created in the books_has_topics table one field has an id for for the book the other for the topic. I also have a user interface for updating the book. I want the user to be able to change the topics.

Code:

this works it give me a list of all the assigned to the book based on the books id.

SELECT  *
    FROM  topic T
    INNER JOIN  book_has_topic BHT  ON T.topic_id = BHT.topic_topic_id
    WHERE  BHT.Book_book_id = [bookid];

This sort of works Not only do i get all the topics not assigned to the bookid I also get all the topics assigned to other books.

SELECT  DISTINCT topic_id, topic_name
    FROM  topic T
    LEFT JOIN  book_has_topic BHT  ON T.topic_id = BHT.topic_topic_id
    WHERE  BHT.book_book_id IS NULL
       or  BHT.book_book_id <> [bookid];
0

I'm more of a MS SQL guy so please excuse me if my syntax is a bit off for mysql, but I would probably go with something like this.

SELECT topic_id, topic_name
FROM topic T
WHERE NOT EXISTS (
  SELECT TOP 1 1
  FROM book_has_topic BHT
  WHERE BHT.topic_topic_id = topic_id
    AND BHT.book_book_id = [bookid])
0

(TOP is not known to MySQL. It's counterpart LIMIT is not needed.)

SELECT  topic_id, topic_name
    FROM topic T
    WHERE NOT EXISTS (
        SELECT 1 FROM book_has_topic BHT
                 WHERE book_id = [bookid]
                   AND topic_id = T.topic_id
                     )

Tips on optimal many:many schema.

Possibly you were close on the LEFT JOIN approach:

SELECT  DISTINCT topic_id, topic_name
    FROM  topic T
    LEFT JOIN  book_has_topic BHT
           ON T.topic_id = BHT.topic_topic_id
          AND  BHT.book_book_id = [bookid]
    WHERE  BHT.book_book_id IS NULL

Note 3 changes to AND BHT.book_book_id = [bookid] -- shange to AND, move to ON, change tok =.

But this probably still needs DISTINCT. Hence using a "semi-join" (EXISTS) is more efficient.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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