CODEWITHSUNDEEP
sqlsql-serverselect
noelicus
noelicus

Reputation: 15055

Selecting missing entries in SQL Server

I have a table of people:

person_id | name
1         | bob
2         | jane

A table of questions they can answer:

qu_id | question
1     | How old are you?
2     | How tall are you?

And a table of answers:

answer_id | qu_id | person_id | answer
10        | 1     | 2         | 24

What query will return a list of people and questions that have not been answered:

For example, Unanswered questions:

qu_id | person_id
1     | 1
2     | 1
2     | 2

(I am using SQL Server Express 2012)

Upvotes: 1

Views: 81

Answers (3)

John Woo
John Woo

Reputation: 263733

SELECT  b.qu_ID, a.person_ID
FROM    people a
        CROSS JOIN question b
        LEFT JOIN answer c
            ON  a.person_ID = c.person_ID AND
                b.qu_ID = c.qu_id
WHERE   c.person_ID IS NULL
ORDER   BY b.qu_id, a.person_id

RESULT

╔═══════╦═══════════╗
║ QU_ID ║ PERSON_ID ║
╠═══════╬═══════════╣
║     1 ║         1 ║
║     2 ║         1 ║
║     2 ║         2 ║
╚═══════╩═══════════╝

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32170

SELECT p.person_id, q.qu_id
FROM Person p
CROSS JOIN Question q

EXCEPT

SELECT person_id, qu_id
FROM Answers

Upvotes: 4

lc.
lc.

Reputation: 116498

SELECT q.qu_id, p.person_id
FROM people AS p
CROSS JOIN questions AS q
WHERE NOT EXISTS 
(
    SELECT 1 
    FROM answers AS a 
    WHERE a.qu_id = q.qu_id 
    AND a.person_id = p.person_id
)

Or,

SELECT q.qu_id, p.person_id
FROM people AS p
CROSS JOIN questions AS q
EXCEPT
SELECT qu_id, person_id
FROM answers

Upvotes: 1

Related Questions