Showing only results common to all categories selected

Databases
1

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:

Geographic Scale

  • City
  • County
  • Neighborhood
  • State
  • Town

Transportation Mode

  • Auto
  • Bicycle
  • Bus
  • Rail
  • Transit

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.

2

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

3

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!

4

Ok, so sorry to dredge this ancient thread, but it’s a project I now have to revisit, and some of the design parameters have changed since I last worked on it (I got pulled off of it for a while to work on more critical things). This question is going to take some explaining to get to, so I apologize for that, but if I don’t explain it clearly, rudy will come in and yell at me. :smile:

The current incarnation of the tool works as stated above. You choose a topic, then your desired criteria, and only the results that are common to ALL criteria selected are shown. Therefore, as you select more criteria, the result set “shrinks”.

Given the way this tool is intended to work, you can easily (as you’d probably imagine) end up doing many searches that contain zero results (i.e if you searched on the crtieria “City” with a setting of “Rural”, you might get no results).

The tool is set up in steps. In step one, users choose a Topic. In step 2 they choose a Geographic Scale, in step 3 they choose a Setting, in step 4 they choose a Transportation mode. What I’ve been asked to do is remove the choices that would result in an empty result set. I’ve done this with other apps, but this one is proving rather difficult, and I think it’s because of how I’ve set up my table structure.

Right now all the criteria (Geographic Scale, Setting, Transportation Mode) are all in one category/subcategory table. The table that joins the criteria to the results is set up like this:
[ result id | topic id | criteria id ]

I’m thinking it might be better for me to set up each of those three criteria in its own table, then have tables to join each of those to the resources. Then I can just (hopefully) do inner joins to get rid of most of the choices that would come up empty. Assuming anyone is still reading at this point, am I on the right track, or is there a better way to do this? (assuming it’s even doable)

5

Ok, I’ve made a bit of progress with the following query:

SELECT cvm.metrics.metric
FROM ( ( cvm.metrics
INNER JOIN cvm.met_crit
        ON cvm.met_crit.metric_id = cvm.metrics.metric_id
        )
INNER JOIN cvm.topics
        ON cvm.topics.topic_id = cvm.met_crit.topic_id
        )
INNER JOIN cvm.criteria AS geo
        ON geo.crit_id = cvm.met_crit.crit_id
WHERE cvm.met_crit.topic_id IN (6,7,9)
AND geo.parent_id = 1
GROUP BY cvm.metrics.metric, geo.crit_name
HAVING count(distinct cvm.met_crit.topic_id) = #listlen("6,7,9")#

The 6,7,9 is in a form variable of course, but I hard-coded it for simplicity

The listlen() thing counts the number of topics selected, which the count distinct has to match. Then I can draw a count of possible results. It seems to work, but now I have to implement it for two more steps.