SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    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:

    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.
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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 --
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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!
    <cfset myblog = "http://cydewaze.org/">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •