Suppose we have three tables student table, course table, and teacher table something like this

'student' table

'course' table

'teacher' table

so here I have given the 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

so here which column should be used to join the student table with the course table?

because both tables have std_id and course_id and if I want to show students who have 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 here which table column should be used to join with the teacher table because the student table also has the teacher_id and course table also has the teacher_id so here which table teacher_id should be used here to join with the teacher table?

please let me know guys how can I know which column should be used here to join?

CodePudding user response：

Think of importance going from left to right.

If your main focus is to show the students and which courses they take, then the Students table is on the left. If your main focus is to show courses and which students take them, then the Courses table is on the left. If your main focus is to show Teacher and what courses they teach, then the teacher table is on the left.

From there, use as many left joins as is necessary. So, again, if your main focus is to show students and which courses they take, left outerjoin the Students table to the Courses table on Students.Course_ID = Courses.Course_ID. Then you can left outer join the Courses table to the Teachers table on Course.Teacher_ID = Teacher.Teacher_ID.

Honestly, I think you can axe the Teacher_ID from the Students table, since the teacher is tied to a course and not a student. Similarly, you can remove the Student_ID from both the Course and Teacher tables since that's eventually going to create multiple records. The object of good table construction is really to reduce the possibility of 1:Many joins as much as you can.

CodePudding user response：

Here's an SQL fiddle on how to join everything up. This is one way to do it, for an exact answer, you have to post a more precise question. As I said in my comment, the original data structure needs fixed. There is no need for every table to reference every other table. It is better to keep data segregated by purpose. For example, the teacher table shouldn't reference the student table unless there is some direct relationship between them. In this case, the teacher will have a logical relationship with the course they teach... but there is no reason to link to the student. The student is assigned to a course. The way you have the data structured, there can only be a 1-to-1 relationship between student and course (unless you duplicate a lot of data unnecessarily). I fixed the data structure and then created the JOINs in a logical manner. I listed out all the courses, added who teaches the course, and then provide a list of students enrolled in the course.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE student (
  std_id int not null
  , std_name nvarchar(50) not null
  );
  
CREATE TABLE course (
  course_id int not null
  , course_name nvarchar(50) not null
  , teacher_id int null
);

CREATE TABLE teacher (
  teacher_id int not null
  , teacher_name nvarchar(50) not null
);

CREATE TABLE course_x_student (
  uid int IDENTITY(1,1) not null
  , course_id int not null
  , std_id int not null
);

INSERT INTO student (std_id, std_name)
VALUES (1, 'Ramesh')
  , (2, 'Ganesh')
  , (3, 'Aadesh')
  , (4, 'Nilesh')
;
 
INSERT INTO teacher (teacher_id, teacher_name)
VALUES (1, 'Roy')
  , (2, 'Amit')
;

INSERT INTO course (course_id, course_name, teacher_id)
VALUES (1, 'JAVA', 1)
  , (2, 'JAVASCRIPT', 1)
  , (3, 'VB.NET' , 2)
;

INSERT INTO course_x_student (std_id, course_id)
VALUES (1, 1)
  , (2, 1)
  , (3, 3)
  , (4, 3)
;

Query 1:

SELECT 
  c.*
  , t.teacher_name
  , STUFF((
      SELECT ','   CAST(s.std_id as nvarchar)   ':'   s.std_name
      FROM student as s
        INNER JOIN course_x_student as cxs
          ON cxs.std_id = s.std_id
          AND cxs.course_id = c.course_id
      FOR XML PATH('')
    ),1,1,'') as student_list
FROM course as c
  LEFT OUTER JOIN teacher as t
    ON t.teacher_id = c.teacher_id

Results:

| course_id | course_name | teacher_id | teacher_name |      student_list |
|-----------|-------------|------------|--------------|-------------------|
|         1 |        JAVA |          1 |          Roy | 1:Ramesh,2:Ganesh |
|         2 |  JAVASCRIPT |          1 |          Roy |            (null) |
|         3 |      VB.NET |          2 |         Amit | 3:Aadesh,4:Nilesh |