SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Threaded View

  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 09:42. Reason: removed extra parameters


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
  •