I have 3 tables - Thing, Category and Thing_Category.
Thing - id, name Category - id, name Thing_Category - thing_id, category_id
Actually I have some similar but more complicated tables, but I'm trying to keep this simple. My application is going to search this data and return things that are in categories. I've got a search form where you can pick all the categories that you want your search to filter on. The problem I'm having is that I want to parameterise the type of the search with an AND/OR. Depending on how an input field is poplulated I want to retrieve all things that feature any of the categories - the "OR", and I've got that covered.
select thing.* from thing, thing_category where thing.id = thing_category.thing_id and (thing_category.category_id = X) or (thing_category.category_id = Y) or (thing_category.category_id = Z)
It's the "AND" I'm having trouble with. I want to retrieve a list of things match all of the categories. Can anyone suggest a way of picking out all the thing_ids from the thing_category table that match at least a variable number of categories?
I think there a few answers to your question. The "goodness" of the answer for you depends on your needs and data I would say.
Here is one idea
SELECT DISTINCT thing.* FROM thing, thing_category AS A, thing_category AS B, thing_category AS C WHERE thing.id = A.thing_id AND A.thing_id = B.thing_id AND B.thing_id = C.thing_id AND A.category_id = X AND B.category_id = Y AND C.category_id = Z
So basically a couple of self joins but these can be expensive. And "dynamic" it isn't.
But it will do what you want.
Other than that some inner queries of some nature I think will be required.