SQL return values from one table depending on another table

Question

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

If the user doesn't exist, then you can't use ir.UserId = because ir.UserId will be null. — Dale K
– Dale K, Commented Jun 4, 2020 at 7:52

Karan · Accepted Answer · 2020-06-04 08:04:47Z

Update your WHERE condition as WHERE ir.UserId IS NULL. It will return all challenges that the user hasn't attempted. Also update ON and use ic.Id = ir.ChallengeId AND ir.UserId = 'cfc1d26c-c091-4b17-aaa3-31e8f5232cf9' to get challanges attempted by user.

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

Dale K · Accepted Answer · 2020-06-04 08:12:21Z

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();

Dale K · Accepted Answer · 2020-06-04 08:21:24Z

You have to join for the results from that user, and use the WHERE to check that no match has been found.

SELECT ic.* from IdentificationChallenges ic LEFT JOIN IdentificationResults ir ON ic.Id = ir.ChallengeId and ir.UserId = 'cfc1d26c-c091-4b17-aaa3-31e8f5232cf9' WHERE ir.Id is null

PS: why do you order by newid() when you don't return that newly created id on the result?

order by newid() I assume will return the challenges in a random order...

Collectives™ on Stack Overflow

SQL return values from one table depending on another table

3 Answers 3

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Related