hello everyone I just want to know here suppose we have three tables student table, course table, and teacher table something like this 'student' table

std_id  std_name  course_id  teacher_id  
1       Ramesh      1          1
2       Ganesh      1          3
3       Aadesh      3          3 
4       Nilesh      3          1
5       Sonam       3          4
6       Abhi        2          4
7       Anil        2          4

'course' table

course_id  course_name   std_id  teacher_id
1           JAVA          1        1 
2           JAVASCRIPT    1        2
3           VB.NET        1        1
4           C#.NET        5        2
5           PYTHON        5        4
6           SAP           6        4
7           C             6        1

'teacher' table

teacher_id  teacher_name  course_id  std_id
1            Roy           1          1
2            Amit          2          1
3            John          1          5
4            Yogesh        3          5
5            Rocky         3          1

so here i have given three tables so now i want to show students who have the courses and teachers so here you can see in the 'student' table we have std_id and course_id and in the 'course' table we have course_id and std_id so as you can see here in the student table we have std_id and course_id and in the course table we have course_id and std_id as well so here which column should be used to join student table with the course table because both tables have std_id and course_id and if i want to show students who has courses so which column should be used here to join the student table and the course table and here i also want to show teachers so here in the teacher table we have teacher_id and course_id and std_id so here of which table column should be used here to join with teacher table because student table also has the teacher_id and course table also has the teacher_id so here of which table teacher_id should be used here to join with teacher please let me know guys how can i know which column should be used here to join?

CodePudding user response：

Given the current schema, a full join would look like this:

select <list your columns here>
from student
join course
on student.course_id = course.course_id and student.teacher_id = teacher.teacher_id
join teacher
on course.teacher_id = teacher.teacher_id and course.course_id = teacher.course_id and student.std_id = teacher.std_id

However, your schema seems to be flawed, as it suggests that a student is taking a single course and a course is taught by a single teacher. Read about m:n relations and consider refactoring your schema.

CodePudding user response：

SELECT student.std_id,student.std_name,course.course_name,teacher.teacher_name 
FROM student 
INNER JOIN course ON student.std_id=course.std_id
INNER JOIN teacher ON student.std_id=teacher.std_id;

This will return the students having courses and teachers, As first inner join produce a temporary table which contains students who has courses and then second inner join produce final temporary table which contains students who has courses and teachers. Hope above sql query is the solution of your question.

Note: You could do database normalization as here many-to-many relationship has been established in all three tables as below. You can create 3 more junction table between all three tables which ensures efficient data processing and data integrity, as well as data analysis tasks.

1. A Teacher can have many courses and teach to many students.
2. A Student can have many courses and can be taught by many teachers.
3. A course can be taught by many teachers and can be opt by many students.