Bowlers Who Took a Caught-and-Bowled Wicket — SQL Easy Problem

Problem statement

In a caught and bowled dismissal the bowler catches the ball off their own delivery, so the wicket's fielder_id is the same player as its bowler_id.

Return the bowler_id of every bowler who has taken at least one caught-and-bowled wicket, each bowler once, ordered by bowler_id ascending.

Tables

Table: Dismissal

Primary key: match_id, wicket_no.

One row per wicket credited to a bowler in a college cricket league. fielder_id is the player who took the catch, or NULL when no fielder was involved (bowled, LBW).

Examples

Example 1

Dismissal

Output

How to solve Bowlers Who Took a Caught-and-Bowled Wicket

The condition compares two columns of the same row: a wicket is caught-and-bowled when fielder_id = bowler_id. No join or subquery is needed — WHERE fielder_id = bowler_id keeps exactly those wickets.

Bowled and LBW wickets have fielder_id NULL. NULL = bowler_id is unknown rather than true, so those rows are dropped without any extra test — which is what we want, since nobody caught anything.

A bowler can take several caught-and-bowled wickets, and the statement wants each bowler once, so the kept rows are collapsed with SELECT DISTINCT bowler_id. GROUP BY bowler_id removes duplicates the same way. Finally ORDER BY bowler_id fixes the order the statement asks for.

A different approach groups every wicket by bowler first and keeps the groups containing at least one such wicket: HAVING SUM(fielder_id = bowler_id) > 0. In MySQL a comparison is 1, 0 or NULL, and SUM skips the NULLs, so the sum counts the caught-and-bowled wickets. It builds a group for every bowler rather than filtering first, so the WHERE version is usually lighter, but both are a single scan plus the cost of removing duplicates and sorting: O(n log n).

Reference solution (MySQL)

SELECT DISTINCT bowler_id
FROM Dismissal
WHERE fielder_id = bowler_id
ORDER BY bowler_id

Another way

SELECT bowler_id FROM Dismissal WHERE fielder_id = bowler_id GROUP BY bowler_id ORDER BY bowler_id

Another way

SELECT bowler_id FROM Dismissal GROUP BY bowler_id HAVING SUM(fielder_id = bowler_id) > 0 ORDER BY bowler_id