Get a list of questionnaires with a missing answer

Question

This is not a good title (editing is welcome), but here is a summary. I currently have a good request, but maybe an easier way to do this than what I am currently using. I have a table "Answer" with columns "QuestionIDFK, Answer, QuestionnaireIDFK". The data in the table is similar to

QuestionIDFK    Answer    QuestionnaireIDFK
1               N         1
2               N         1
3               N         1
1               Y         2
2               Y         2
3               Y         2
1               N         3
3               N         3
1               Y         4
3               Y         4

which means that users answered question 2 for questionnaires 1 and 2, but did not provide an answer to question 2 for questionnaires 3 and 4. What I need is a list of questionnaire identifiers that do not have an answer for question 2. Thus, the expected result

QuestionnaireWithMissingAnswer2
3
4

I use this query below, which works fine, but I wonder if there is an easier way:

SELECT distinct a.QuestionnaireIDFK AS QuestionnaireWithMissingAnswer2 
FROM Answer a
inner join (
SELECT t.QuestionnaireIDFK,
       STUFF(ISNULL((SELECT ', ' + convert(nvarchar, x.QuestionIDFK)
                FROM Answer x
                WHERE x.QuestionnaireIDFK = t.QuestionnaireIDFK
                GROUP BY x.QuestionIDFK
                FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') 
                AS QuesList
     FROM Answer t
     GROUP BY t.QuestionnaireIDFK) z 
ON z.QuestionnaireIDFK = a.QuestionnaireIDFK AND z.QuesList NOT LIKE '%2%'

Answer 1

not exists group by having:

select QuestionnaireIDFK
from answer
group by QuestionnaireIDFK
having sum(case when QuestionIDFK = 2 then 1 else 0 end) = 0;

, № 2 ( , , ).

not exists ( a left outer join):

select distinct QuestionnaireIDFK
from answer
where not exists (select 1
                  from answer a2
                  where a.QuestionnaireIDFK = a2.QuestionnaireIDFK and
                        a2.QuestionIDFK = 2
                 );

- distinct , .

Answer 2

, , , , where:

select q.*
from Questionnaire q
left join Answer a on a.QuestionnaireIDFK = q.id
    and a.QuestionIDFK = 2
where a.QuestionnaireIDFK is null

, where, , , . "" , ( where ).

, , , .

, .