Showing only results common to all categories selected
It's a weird thread title, I know, but it's an even weirder problem, and I'll do my best to not do my usual disasterous way of explaining it. I apologize in advance if this makes no sense.
I've been given a database to build a web tool around, and it has a table of resources, a table of topics, and a table of criteria. The criteria table is laid out like a category/subcategory list (category ID, category name, parent ID). The resources table just has a resource name and ID. The topics table also has a name and ID. Then there's a final table that joins resource ID, topic ID, and criteria ID, since some critera may only apply to a resource under certain topics.
Each resource can be listed under multiple critera, and which criteria depends on the topic.
Let's take a sample resource, "Barriers to pedestrians and cyclists (delay and risk)".
This resource may appear under the topics "Safety" and "Accessibility". If someone checks either of those two topics on the web form, they'll see that resource.
On the next part of the form, the user get to specifty criteria. Criteria looks like this:
Now, with the sample resource above, it'll appear if you select "Bicycle" under transportation mode and probably any of the choices under Geographic Scale. Now, this is the weird part...
The intended behavior of the form is that if you select a critera that does NOT apply, the resource is NOT shown. So if I chose Bicycle and Rail, the resource would not show because it does not apply to both of those criteria.
I have no idea how to do this in SQL, or if it's even possible. The user could choose several topics, several criteria, and only the resources matching ALL selected options should appear.
Right now I have some quirky (and messy) programming code that does this, but I'd really like to know if there's a proper way to do it in SQL.