Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2011
    Posts
    9

    Unanswered: Need som help on joining

    This is how far I have come at the moment:

    SELECT a1.OptionID, a2.OptionID, COUNT(*) AS Options FROM Answer AS a1
    JOIN Answer AS a2 ON a1.AnswerHeaderID = a2.AnswerHeaderID
    WHERE a1.OptionID IN (SELECT OptionID FROM [Option] WHERE QuestionID = @q1) AND a2.OptionID IN (SELECT OptionID FROM [Option] WHERE QuestionID = @q2)
    GROUP BY a1.OptionID, a2.OptionID
    ORDER BY a1.OptionID, a2.OptionID

    Basically what I am trying to accomplish is that I've got a survey that people can answer and I want to be able to join TWO questions with each other.

    Let's say Question #1 is SEX - 2 options, male or female
    Let's say Question #2 is AGE - 6 options, 0-25, 25-30, 31-40, 41-50, 51-60 or 60+

    Let's also say that the optionID are like this:
    OptionID 1 -> Male
    OptionID 2 -> Female
    OptionID 3 -> 0-25
    OptionID 4 -> 25-30
    OptionID 5 -> 31-40
    OptionID 6 -> 41-50
    OptionID 7 -> 51-60
    OptionID 8 -> 60+

    There are NO Females that have answered that are 41-50, all other combinations exist in the Answer table

    The result of the above query is:

    Code:
    OptionID	OptionID	Options
    1	3	7
    1	4	10
    1	5	6
    1	6	7
    1	7	2
    1	8	2
    2	3	4
    2	4	3
    2	5	2
    2	7	2
    2	8	7
    The result tells you that there are 7 male that are 0-25 that answered the survey.. and so on.

    But since there are no female aged 41-50 that answered the survey it does not show up in the result.

    My question is how to change the query so that the result would show the below as well

    Code:
    OptionID	OptionID	Options
    2	6	0
    For referense. To just check the number of let's say Male and Female that has answered without joining a second question I use the following working query:

    SELECT COUNT(a.OptionID) AS Options FROM [Option] o LEFT JOIN Answer a ON o.OptionID = a.OptionID WHERE o.QuestionID = @QuestionID GROUP BY o.OptionIndex ORDER BY o.OptionIndex

    Also note that I would like to ORDER BY OptionIndex rather than the OptionID in the query just like I do on the above working query since that is the actual order of the options. In this simple example the OptionID happens to be the correct order as well, but that does not need to be the case.

    Thank you VERY much in advance.

    EDIT:
    I would also like to add that the onlything I really need as result is a 1 column result:

    Code:
    Options
    7
    10
    6
    7
    2
    2
    4
    3
    2
    0
    2
    7
    Last edited by Marty78; 01-12-11 at 14:29.

  2. #2
    Join Date
    Jan 2011
    Posts
    9
    No one that has got any idea how I would accomplish this?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT o1.OptionID
         , o2.OptionID
         , COUNT(a2.OptionID) AS Options 
      FROM [Option] AS o1
    CROSS 
      JOIN [Option] AS o2
    LEFT OUTER
      JOIN Answer AS a1
        ON a1.OptionID = o1.OptionID
    LEFT OUTER
      JOIN Answer AS a2 
        ON a2.OptionID = o2.OptionID
       AND a2.AnswerHeaderID = a1.AnswerHeaderID
     WHERE o1.QuestionID = @q1 
       AND o2.QuestionID = @q2
    GROUP 
        BY o1.OptionID
         , o2.OptionID
    ORDER  
        BY o1.OptionID
         , o2.OptionID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2011
    Posts
    9
    You're a star! Thank you thank you!

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    too late
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •