Trying to get common keys that match on multiple records
Okay, I know the title is a little weird but I'm trying to come up with a good way to describe my question.
Summary: I am building a system that has quizzes that is configurable by an admin. The tables work fine for recording responses, but I'm having trouble on the query for searching for members that have 'like' answers. More specifically, like a matchmaking service, I need a query that will allow someone to say,
"Show me everyone that answered question 1 as 'true', question 3 as 'San Diego', and question 4 as either 'Mexican', 'Italian', or 'vegetarian'."
Here are the tables:
type (checkbox, radio, dropdown, mulitple-select,text)
I've parred down the tables to the important fields, and I would hope things are self-explanatory but I'll give some quick notes.
So Quiz will hold individual quizzes. So one quiz could be 'Favorites' and another could be 'Trivia'. Each Quiz will have multiple Questions where text is the question being asked like "What is your favorite food?". Type will designate what type of question/how to display possible answers. So type=checkbox means it's an agree/disagree type question, while type=radio means there are multiple answers shown and the member can only select one. For types radio, dropdown, and multiple-select, all the possible answers are stored in Answer.
Finally, the member's responses are stored in Response.
Given that, I have a form that let's people search other people's answers.
So my question is, I need a query that will give me the user_id's of members whose responses match all the searched questions.
select user_id where
question 1 = 1 or 2
question 2 like "san diego"
question 4 = 2, 7 or 12
question 8 = 30
But keep in mind that there can be any number of quizzes, any number of questions, and they don't need to search every question.
Extra points if I knew how many matches occurred. Not required, but still nice to know.