All Valid Triplets That Can Represent A Country Problem
Description
LeetCode Problem 1623.
Table: SchoolA
1
2
3
4
5
6
7
8
9
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| student_name | varchar |
+---------------+---------+
student_id is the primary key for this table.
Each row of this table contains the name and the id of a student in school A.
All student_name are distinct.
Table: SchoolB
1
2
3
4
5
6
7
8
9
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| student_name | varchar |
+---------------+---------+
student_id is the primary key for this table.
Each row of this table contains the name and the id of a student in school B.
All student_name are distinct.
Table: SchoolC
1
2
3
4
5
6
7
8
9
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| student_name | varchar |
+---------------+---------+
student_id is the primary key for this table.
Each row of this table contains the name and the id of a student in school C.
All student_name are distinct.
There is a country with three schools, where each student is enrolled in exactly one school. The country is joining a competition and wants to select one student from each school to represent the country such that:
- member_A is selected from SchoolA,
- member_B is selected from SchoolB,
- member_C is selected from SchoolC, and
- The selected students’ names and IDs are pairwise distinct (i.e. no two students share the same name, and no two students share the same ID).
Write an SQL query to find all the possible triplets representing the country under the given constraints.
Return the result table in any order.
The query result format is in the following example.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SchoolA table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1 | Alice |
| 2 | Bob |
+------------+--------------+
SchoolB table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 3 | Tom |
+------------+--------------+
SchoolC table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 3 | Tom |
| 2 | Jerry |
| 10 | Alice |
+------------+--------------+
Result table:
+----------+----------+----------+
| member_A | member_B | member_C |
+----------+----------+----------+
| Alice | Tom | Jerry |
| Bob | Tom | Alice |
+----------+----------+----------+
Let us see all the possible triplets.
- (Alice, Tom, Tom) --> Rejected because member_B and member_C have the same name and the same ID.
- (Alice, Tom, Jerry) --> Valid triplet.
- (Alice, Tom, Alice) --> Rejected because member_A and member_C have the same name.
- (Bob, Tom, Tom) --> Rejected because member_B and member_C have the same name and the same ID.
- (Bob, Tom, Jerry) --> Rejected because member_A and member_C have the same ID.
- (Bob, Tom, Alice) --> Valid triplet.
MySQL Solution
1
2
3
4
5
select SchoolA.student_name as member_A, SchoolB.student_name as member_B, SchoolC.student_name as member_C
from SchoolA, SchoolB, SchoolC
where (SchoolA.student_id != SchoolB.student_id and SchoolA.student_id != SchoolC.student_id and
SchoolB.student_id != SchoolC.student_id and SchoolA.student_name != SchoolB.student_name and
SchoolA.student_name != SchoolC.student_name and SchoolB.student_name != SchoolC.student_name)
LeetCode Database - Easy
LeetCode 1407
LeetCode 1435
LeetCode 1484
LeetCode 1495
LeetCode 1511
LeetCode 1517
LeetCode 1527
LeetCode 1543
LeetCode 1565
LeetCode 1571
LeetCode 1581
LeetCode 1587
LeetCode 1607
LeetCode 1623
LeetCode 1633
LeetCode 1661
LeetCode 1667
LeetCode 1677
LeetCode 1683
LeetCode 1693
LeetCode 1729
LeetCode 1731
LeetCode 1741
LeetCode 1757
LeetCode 1777
LeetCode 1789
LeetCode 1795
LeetCode 1809
More LeetCode Database
MySQL Tutorials