CODEWITHSUNDEEP
sql-servert-sqlexists
Charles Okwuagwu
Charles Okwuagwu

Reputation: 10866

What T-sql query can i use to ensure all ids from one table are present in a second table

I have a table of Mandatory questions.

I have another table for Student Answers.

How do I write an sql query to check if ALL mandatory question-ids are present in the Answers table for a particular Student?

I have tried:

if exists(select * from scores s where s.matric=@matric and s.qid not in (select qid from questions q where q.required=1))
    select 0
else
    select 1

Upvotes: 1

Views: 46

Answers (2)

TT.
TT.

Reputation: 16146

Here is an alternative way of writing this, using EXCEPT between the question id set and the answered id set:

IF EXISTS (
    SELECT qid FROM questions WHERE required=1
    EXCEPT
    SELECT qid FROM scores WHERE matric=@matric
)
    SELECT 'Some or all questions unanswered';
ELSE
    SELECT 'All questions answered';

Upvotes: 2

Stefan Zvonar
Stefan Zvonar

Reputation: 4319

Have not run to test, but at first glance, I'd flip the select statement around, similar to below:

SELECT 1
FROM Questions
WHERE [Required] = 1
    AND qid NOT IN (
                        SELECT qid
                        FROM Scores s
                        WHERE s.matric = @matric
                     )

Upvotes: 2

Related Questions