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:
x
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
Advertisement
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();