Trying to get common keys that match on multiple records

[Cyberfunkr]

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:
Tables: Users
user_id
login
password

Table: Quiz
quiz_id
title
description

Table: Question
question_id
quiz_id
text
type (checkbox, radio, dropdown, mulitple-select,text)

Table: Answer
answer_id
question_id
text

Table: Response
user_id
quiz_id
question_id
answer_id
value

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.

In pseudo-code:

select user_id where
question 1 = 1 or 2
AND
question 2 like "san diego"
AND
question 4 = 2, 7 or 12
AND
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.

Thanks

[r937]

you have 4 criteria to match for a user, so retrieve them, count them, and reject the user if all 4 weren't found

SELECT r.user_id
  FROM Response AS r
INNER
  JOIN Answer AS a
    ON a.answer_id = r.answer_id
 WHERE r.question_id = 1 AND a.text IN ( '1','2' )
    OR r.question_id = 2 AND a.text LIKE '%San Diego%'
    OR r.question_id = 4 AND a.text IN ( '2','7','12' )
    OR r.question_id = 8 AND a.text = '30'
GROUP
    BY r.user_id
HAVING COUNT(*) = 4

[Cyberfunkr]

Update:

So I instituted your solution and it seemed to work fine during all my testing, but when put on the live server, it kills the system.

Here is an example of the final query:

SELECT DISTINCT Users.*
FROM Users 
WHERE Users.validated = 1
AND Users.deleted <> 1
AND Users.access > 2
AND Users.country = 254
AND Users.region = 126
AND Users.user_id IN 
	(
    SELECT r.user_id
    FROM Response r
    WHERE ( r.questionid = 1 AND ( r.answer_id = 1 ) )
    OR ( r.questionid = 2 AND ( r.answer_id = 12 OR r.answer = 13 ) )
    GROUP BY r.user_id
    HAVING COUNT(*) = 2
	)
GROUP BY Users.user_id
ORDER BY Users.login

But here's the thing: Running just the sub-query is fine. Running the main query (minus the IN ) runs fine. But running the whole thing brings MySQL to it's knees. I usually end up needing to restart the server.

So any advice you can give would be appreciated. I really need to get this search working right.

Thanks

[r937]

remove both the DISTINCT and the GROUP BY

then, if necessary, remove the ORDER BY

[Cyberfunkr]

Thank you for your help. Unfortunately, it still didn't solve the crashing. But I did want to say I finally found a solution that worked. The key seems to be removing the IN () sub-query.

Here is the final code.

SELECT u.*, count( u.user_id ) as matches
FROM Users u
INNER JOIN Response r
ON u.user_id = r.user_id
WHERE u.validated = 1
AND u.deleted <> 1
AND u.access > 2
AND u.country = 254
AND u.region = 126
AND (
		( r.questionid = 1 AND ( r.answer_id = 1 ) )
	OR	( r.questionid = 2 AND ( r.answer_id = 12 OR r.answer_id = 13 ) )
	)
GROUP BY u.user_id
HAVING matches = 2
ORDER BY u.login

I'm going to run some more tests but that seems to be the final solution.

Thanks again for your quick help.

/Cyberfunkr