In MySQL, how can we maintain data-driven table relationship using joins?

Actually, sometimes we can avoid data-driven relationships in tables and we need to join them. It can be done with the help of CASE statement in the SELECT list to handle the joining possibilities. To understand it, we are taking the example of three data-driven tables namely ‘Student_Detail’ which have the following data −

mysql> Select * from student_detail;
+----+---------+
| Id | Name    |
+----+---------+
| 1  | Harshit |
| 2  | Rahul   |
| 3  | Aarav   |
+----+---------+
3 rows in set (0.00 sec)

Now, we have the three tables namely ‘Student_Harshit’, ‘Student_Rahul’, ‘Student_Aarav’ which have the remarks for the students Harshit, Rahul and Aarav respectively. They have the following data −

mysql> Select * from Student_Harshit;
+----+-----------+
| Id | Remarks   |
+----+-----------+
| 1  | Excellent |
+----+-----------+
1 row in set (0.00 sec)

mysql> Select * from Student_Rahul;
+----+---------+
| Id | Remarks |
+----+---------+
| 2  | Average |
+----+---------+
1 row in set (0.00 sec)

mysql> Select * from Student_Aarav;
+----+-------------+
| Id | Remarks     |
+----+-------------+
| 3  | Intelligent |
+----+-------------+
1 row in set (0.00 sec)

Now, the following query will handle these data-driven tables −

mysql> Select sd.id, sd.name, CASE name WHEN 'Harshit' THEN H1.Remarks WHEN 'Rahul' THEN R1.Remarks WHEN 'Aarav' THEN A1.Remarks ELSE 'Error' END as REMARKS FROM Student_detail AS sd LEFT JOIN Student_Harshit AS H1 ON sd.id = H1.id LEFT JOIN Student_Rahul AS R1 ON sd.id = R1.id LEFT JOIN Student_Aarav AS A1 on sd.id = A1.id;
+----+---------+-------------+
| id | name    | REMARKS     |
+----+---------+-------------+
| 1  | Harshit | Excellent   |
| 2  | Rahul   | Average     |
| 3  | Aarav   | Intelligent |
+----+---------+-------------+
3 rows in set (0.00 sec)