'Compound' JOIN Query

[Roger Rickard]

Hi,

I am looking to perform a JOIN on some tables. The problem I am having is in JOINing two tables were one row from a table is compared to every row (according to conditions) on another table.

I have three main tables, 'items', 'topics', and 'ratings'. Each item is rated against each topic in the topics table, this is stored in 'ratings' a M2M table for answers. So the ratings table has the fields; item_id, topic_id, and answer_id.

As the topics get answered, the values are sotred into the 'session_answers' table - it's fields are; session_id, topic_id, and answer_id.

I am basically trying to KNOCK-OUT all of the items that don't align themselves with all of the answers provided in 'session_answered'. The best I can do at the moment is find all of the items remaining for a session where there is a correct answer for it, not where all questions that are answered have been answered correctly.

I should be selecting less and less items, so far I have achieved getting more and more.

Is this something I can only achieve with a repeat/loop through a stored procedure? My aim is to create a view that would be called 'items_remaining' so I can just SELECT count(*) FROM items_remaining WHERE session_id = 123.