Joining one table twice and counting records

I have 2 tables. One is master and thee other is a lookup. TblMstr and Cmaster

Tblmstr

Id  QuestionId   ChoiceId ChoiceMaster
1    2             1       Strongly Agree
1    3             2       Disagree
1    4             3       Agree
1    5             11       null
2    2             2       Disagree
2    3             5       Disagree
2    4             9       Agree
2    5             12       null

Cmaster

ChoiceId  QuestionId    ChoiceName
1             2         Strongly Agree
2             2         Disagree
3             2         Agree
4             3         Strongly Agree
5             3         Disagree
6             3         Agree     
7             4         Strongly Agree
8             4         Disagree
9             4         Agree  
10            5         Registered Nurse
11            5         Nurse Practitioner
12            5         Pharmacist

I need to get how many Registered Nurse for each question have Strongly Agree, Disagree and Agree. Similarly for Nurse Practitioners and Pharmacists.

Answer

Hmmm . . . You can join twice. Once to get the occupation and once to get the response on the other questions:

select co.choicename as occupation,
       sum(case when c.choicename = 'Strongly Agree' then 1 else 0 end) as cnt_strongly_agree,
       sum(case when c.choicename = 'Agree' then 1 else 0 end) as cnt_agree,
       sum(case when c.choicename = 'Disagree' then 1 else 0 end) as cnt_disagree
from Tblmstr m join
     Cmaster co
     on co.id = m.choiceid and co.questionid = 5 join
     Cmaster c
     on co.id = m.choiceid and co.questionid <> 5
group by co.choicename