SQL return values from one table depending on another table

I have 2 tables – IdentificationChallenges and IdentificationResults.

The challenges table shows a list of challenges and the results table stores a users attempt at the challenge.

I need a query to return all challenges that the user hasn’t attempted (where the user id doesn’t exist in the results table). My attempt at this is as follows:

SELECT ic.*
from IdentificationChallenges ic
LEFT JOIN IdentificationResults ir ON ic.Id = ir.ChallengeId
WHERE ir.UserId != 'cfc1d26c-c091-4b17-aaa3-31e8f5232cf9'
ORDER BY NEWID()

It does not retrieve any records. Any idea where I’m going wrong?

IdentificationChallenges

Id | ChallengeDateTime | ImageUrl | Description

IdentificationResults

Id | ChallengeId | UserId | ChallengeResult | ResultDateTime

Answer

I would use not exists to get a list of challenges for a specific user which they have yet to complete. The reason is, in your join, you’re actually looking for cases where ir.UserId is null but that won’t return a list relevant to the specific user. The following should do what you need.

DECLARE @ID UNIQUEIDENTIFIER = 'cfc1d26c-c091-4b17-aaa3-31e8f5232cf9';

SELECT IC.*
FROM dbo.IdentificationChallenges IC
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.IdentificationResults IR
    WHERE IR.ChallengeId = IC.Id
    AND IR.UserId = @ID
)
ORDER BY NEWID();