All Valid Triplets That Can Represent a Country - Problem

You are given three tables representing students from three different schools in a country. Each student has a unique ID within their school and a distinct name within their school.

The country wants to select one student from each school to represent them in a competition with the following constraints:

  • member_A must be selected from SchoolA
  • member_B must be selected from SchoolB
  • member_C must be selected from SchoolC
  • The selected students must have pairwise distinct names and IDs (no two students can share the same name or the same ID)

Write a SQL query to find all possible triplets that can represent the country under these constraints.

Table Schema

SchoolA
Column Name Type Description
student_id PK int Unique student identifier within SchoolA
student_name varchar Unique student name within SchoolA
Primary Key: student_id
SchoolB
Column Name Type Description
student_id PK int Unique student identifier within SchoolB
student_name varchar Unique student name within SchoolB
Primary Key: student_id
SchoolC
Column Name Type Description
student_id PK int Unique student identifier within SchoolC
student_name varchar Unique student name within SchoolC
Primary Key: student_id

Input & Output

Example 1 — Multiple Valid Triplets
Input Tables:
SchoolA
student_id student_name
1 Alice
2 Bob
SchoolB
student_id student_name
3 Tom
SchoolC
student_id student_name
3 Jerry
6 Alice
Output:
member_A member_B member_C
1 3 6
2 3 6
💡 Note:

From the cross join, we get several combinations, but only some are valid:

  • Alice(1) + Tom(3) + Jerry(3): Invalid - duplicate ID 3
  • Alice(1) + Tom(3) + Alice(6): Invalid - duplicate name Alice
  • Alice(1) + Tom(3) + Alice(6): Valid - all IDs and names distinct
  • Bob(2) + Tom(3) + Alice(6): Valid - all IDs and names distinct
Example 2 — No Valid Triplets
Input Tables:
SchoolA
student_id student_name
1 Alice
SchoolB
student_id student_name
1 Bob
SchoolC
student_id student_name
2 Alice
Output:
member_A member_B member_C
💡 Note:

The only possible combination is Alice(1) + Bob(1) + Alice(2), but this is invalid because:

  • Student IDs 1 and 1 are duplicated (Alice from SchoolA and Bob from SchoolB both have ID 1)
  • Student names 'Alice' appears in both SchoolA and SchoolC

Since no valid triplets exist, the result is empty.

Constraints

  • 1 ≤ student_id ≤ 1000
  • student_name consists of uppercase and lowercase English letters
  • All student_name within each school are distinct
  • All student_id within each school are unique

Visualization

Tap to expand
Student Triplet Selection ProcessStep 1: Input SchoolsSchoolA1AliceSchoolB3TomSchoolC6JerryStep 2: Cross JoinAll Combinations (A × B × C)(1,Alice) × (3,Tom) × (6,Jerry)WHERE FilterStep 3: Apply ConstraintsDistinct ID & Name Check✓ IDs: 1 ≠ 3 ≠ 6 (all distinct)✓ Names: Alice ≠ Tom ≠ Jerry (all distinct)Valid TripletStep 4: ResultValid Country RepresentativesA=1, B=3, C=6Each valid triplet has completely distinct student IDs and namesSQL Query Logic1. CROSS JOIN creates cartesian product: SchoolA × SchoolB × SchoolC2. WHERE filters for distinct IDs: a.student_id ≠ b.student_id ≠ c.student_id3. WHERE filters for distinct names: a.student_name ≠ b.student_name ≠ c.student_name4. Result: All valid triplets that can represent the country
Understanding the Visualization
1
Input Tables
Three school tables with students
2
Cross Join
Generate all possible triplet combinations
3
Filter
Apply distinct ID and name constraints
4
Output
Valid triplets for country representation
Key Takeaway
🎯 Key Insight: Use CROSS JOIN to generate all combinations, then filter with WHERE conditions for constraint satisfaction

Related Problems

Asked in
Amazon 12 Microsoft 8
32.0K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen