Select users who have won in different categories-CodePudding

I need to select the users who have won at least 1 round in all of the different competitions.

I have the following table sctructure:

results

id	round_id	user_id	result
1	1	2	1
2	1	1	2

rounds

id	category_id
1	1
2	1
3	2
4	2

categories championship

id	competition_id
1	1
2	2

competitions team

id	competition_name
1	Competition A
2	Competition B

Now let's say Bob has won at least 1 round in both Competition A and B each, he needs to show up in the list. But Joe, who's won 1 round in Competition A but nothing in Competition B, must not show up.

I tried writing a script like this, but I can see the flaw in my logic. It's looking for a row where the round_id is both 1 and 2 is impossible.

SELECT user_id FROM results WHERE 
    (result = 1 AND round_id IN 
        (SELECT id FROM rounds WHERE category_id IN 
            (SELECT id FROM categories WHERE competition_id = 7)
        )
    ) AND 
    (result = 1 AND round_id IN 
        (SELECT id FROM rounds WHERE category_id IN 
            (SELECT id FROM categories WHERE competition_id = 8)
        )
    ) AND 
    (result = 1 AND round_id IN 
        (SELECT id FROM rounds WHERE category_id IN 
            (SELECT id FROM categories WHERE competition_id = 9)
        )
    )
    GROUP BY driver_id

How can I achieve this?

CodePudding user response：

Join results to rounds and categories (competitions is not needed because competition_id exists in categories).
Then filter the rows for only the users who won at least one round and aggregate by user with the condition in the HAVING clause for the user that all 3 competition_ids are returned:

SELECT rs.user_id
FROM results rs
INNER JOIN rounds rd ON rd.id = rs.round_id
INNER JOIN categories cg ON cg.id = rd.category_id
WHERE rs.result = 1 AND cg.competition_id IN (7, 8, 9)
GROUP BY rs.user_id
HAVING COUNT(DISTINCT cg.competition_id) = 3;