SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:
    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
    Last edited by Cyberfunkr; Sep 16, 2009 at 10:42. Reason: removed extra parameters

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you have 4 criteria to match for a user, so retrieve them, count them, and reject the user if all 4 weren't found
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code MySQL:
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    remove both the DISTINCT and the GROUP BY

    then, if necessary, remove the ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code MySQL:
    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


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
  •