# Thread: Need som help on joining

1. Registered User
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
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. Registered User
Join Date
Jan 2011
Posts
9
No one that has got any idea how I would accomplish this?

3. SQL Consultant
Join Date
Apr 2002
Location
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
ON a1.OptionID = o1.OptionID
LEFT OUTER
ON a2.OptionID = o2.OptionID
WHERE o1.QuestionID = @q1
AND o2.QuestionID = @q2
GROUP
BY o1.OptionID
, o2.OptionID
ORDER
BY o1.OptionID
, o2.OptionID```

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

5. Registered User
Join Date
Nov 2004
Posts
1,428