Multi-term queries across many-to-many tables
Posted on 2013-06-24
I am using a database to store "Items" which possess varying numbers of "Assets". Each Item has some fixed columns and may also may have zero, one or more Assets associated with it. Each Asset has a fixed number of columns of different types.
So I have a table of Items, a table of Assets, a many-to-many bridge table between them, and then do a two-stage join on the Item ID and Asset ID. This gives a results set containing one row per Item per Asset, which all works fine.
I can easily find Items that possess a single named Asset, but how do I draft a multi-term SQL query for a combination of named Assets, e.g. "find Items that possess Assets A and B but not C"?
I am sure this is a well-known problem :)