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.
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];