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.
let me explain the solution in general terms, and then you try to put it into practice
the issue with criteria such as "Bicycle and Rail" is that they will exist on separate rows
you could set up a query that does as many joins (minus 1) as there are criteria, but that gets clumsy quickly especially when the number of criteria is variable
the solution involves using a GROUP BY and then counting the number of rows found
so the query would include --
WHERE transportation_mode IN ('bicycle','rail')
this will find the individual rows, either one or bofadem
then you do GROUP BY on the resource and count how many you found -- if this number equals the number of criteria in the IN list, then that resource qualifies
might have to do the above in a subquery, and then join the subquery to your other tables to get the data required
Wow that's interesting rudy!
The way I have it done in my mock-up, I do a something similar, but programatically (I loop through the results, and discard any have fewer results than the number of criteria selected). The problem with that is that I get blank topics in my result set, so I ended up creating a separate resultset (ColdFusion's QueryAdd(), only adding rows when the number of rows equaled the number of criteria selected) and then queried that for the results.
I'm going to try one your way and see if it's cleaner (in case someone else has to get their fingers into this later).
Thanks for the advice!