CODEWITHSUNDEEP
mysqlsqljoin
aadamson
aadamson

Reputation: 13

SQL: Find all rows in a table for which all rows in another table that refer to the original row have a certain property

I have the two tables, challenges and contestants. Contestants have an id, a challenge_id, and a user_id column.

Given a set of user ids, I want to find the set of challenges such that a challenge is in the set if all contestants whose challenge_ids are equal to the challenge's id have a user_id in the given set of user ids.

I feel like I need some sort of an IN ALL statement, but of course, that doesn't exist.

Any ideas on how to find that list of rows?

Upvotes: 1

Views: 31

Answers (1)

Charles Bretana
Charles Bretana

Reputation: 146541

Isn't this the same as "find the set of challenges where all contestants with that challenge_id are in the given set of user ids.",

to find a set where ALL rows meet some condition, you need only find the set where No member does NOT meet the condition, so this is the same as:

"find the set of challenges where there are NO contestants with that challenge_id who are NOT in the given set of user ids.",

Select distinct challenge_Id
From Contestents c
Where Not exists (Select * From Contestents 
                  Where challenge_Id = c.challenge
                     And user_id not In ([List of UserIds]))

Upvotes: 0

Related Questions