Students Who Attended Every Fest Workshop — SQL Medium Problem

Problem statement

At the college tech fest, students check in to workshops by scanning a QR code at the door. A student who steps out and comes back scans again, so the same student and workshop can appear more than once.

Return the roll_no of every student who checked in to every workshop listed in Workshop. Return the rows in any order.

Tables

Table: Workshop

Primary key: workshop_id.

One row per workshop at the fest.

Table: CheckIn

Primary key: scan_id.

One row per QR scan. Every workshop_id here is in Workshop.

Examples

Example 1

Workshop

CheckIn

Output

How to solve Students Who Attended Every Fest Workshop

"Attended every workshop" is relational division, and the counting form is the easiest to write. Group the scans by student; the student qualifies when the number of different workshops they scanned into equals the number of workshops in the catalogue, which a scalar subquery (SELECT COUNT(*) FROM Workshop) supplies.

DISTINCT is essential. Student 502 in the example scanned three times but into only two workshops; COUNT(*) = 3 would wrongly let them through. Counting distinct ids can only match the total when every workshop is covered, because every workshop_id in CheckIn is a real workshop — without that guarantee you would have to join to Workshop first so that unknown ids could not stand in for missing ones.

The double NOT EXISTS is the textbook form: keep a student for whom there is no workshop with no matching scan. It reads like the definition and needs no counting, so duplicates never matter. The third query builds every student × workshop pair with a CROSS JOIN, attaches scans with a LEFT JOIN, and keeps students with no missing pair.

The grouped version reads CheckIn once and Workshop once; the NOT EXISTS form probes CheckIn once per student and workshop, which is fast with an index on (roll_no, workshop_id).

Reference solution (MySQL)

SELECT roll_no
FROM CheckIn
GROUP BY roll_no
HAVING COUNT(DISTINCT workshop_id) = (SELECT COUNT(*) FROM Workshop)

Another way

SELECT DISTINCT c.roll_no FROM CheckIn c
WHERE NOT EXISTS (
  SELECT 1 FROM Workshop w
  WHERE NOT EXISTS (SELECT 1 FROM CheckIn x WHERE x.roll_no = c.roll_no AND x.workshop_id = w.workshop_id)
)

Another way

SELECT s.roll_no
FROM (SELECT DISTINCT roll_no FROM CheckIn) s
CROSS JOIN Workshop w
LEFT JOIN CheckIn c ON c.roll_no = s.roll_no AND c.workshop_id = w.workshop_id
GROUP BY s.roll_no
HAVING SUM(c.scan_id IS NULL) = 0