CODEWITHSUNDEEP
mysqlsql
m5kev4n
m5kev4n

Reputation: 571

query from 5 joined tables

So I have 5 tables as follows :

CREATE TABLE student (
  id   serial PRIMARY KEY,
  name varchar(255) NOT NULL
  -- other columns, constraints, etc...
);
CREATE TABLE teacher (
  id   serial PRIMARY KEY,
  name varchar(255) NOT NULL
  -- other columns, constraints, etc...
);
CREATE TABLE course(
  id   serial PRIMARY KEY,
  name varchar(255) NOT NULL
  -- other columns, constraints, etc...
);
CREATE TABLE student_course (
  student_id integer NOT NULL REFERENCES student(id),
  course_id  integer NOT NULL REFERENCES course(id)
);
CREATE TABLE teacher_course (
  teacher_id integer NOT NULL REFERENCES teacher(id),
  course_id  integer NOT NULL REFERENCES course(id)
);

and I want to query which student study what course with which teacher.

SELECT s.name, c.name, t.name
FROM student s
  JOIN student_course sc ON s.id = sc.student_id
  JOIN course c ON sc.course_id = c.id
  JOIN teacher_course tc ON c.id = tc.course.id
  JOIN teacher t ON tc.teacher_id = t.id

but the result is not what i intended. like 1 student studies one subject with 2 different teachers. It's not the input problem, cos i check and there's no duplicate input in both teacher_course, and student_course. So i think the problem is with my query. Can someone tell me where i went wrong?

Here's the output

Upvotes: 0

Views: 362

Answers (2)

Akankha Ahmed
Akankha Ahmed

Reputation: 121

try this 

 SELECT 
    s.name as Student_Name,
    c.name as Course_Name,
    t.name as Teacher_Name

From student s

inner join student_course sc on s.id = sc.student_id 
inner join course c on sc.course_id = c.id
inner join teacher_course tc on tc.course_id =c.id 
inner join teacher t on tc.course_id = t.id;

result :

enter image description here

Upvotes: 1

user7941334
user7941334

Reputation:

Your sql statement seems correct. I used LEFT JOINs though, because JOIN is by default an INNER JOIN (see What is the default MySQL JOIN behaviour, INNER or OUTER?). But, based on your table syntaxes, I applied the sql statement both with JOINs and with LEFT JOINs and I received the same results. Anyway, my sql statement with LEFT JOINs is:

SELECT 
    s.*,
    c.*,
    t.*
FROM student AS s
LEFT JOIN student_course AS sc ON sc.student_id = s.id
LEFT JOIN course AS c ON c.id = sc.course_id
LEFT JOIN teacher_course AS tc ON tc.course_id = c.id
LEFT JOIN teacher AS t ON t.id = tc.teacher_id;

If the problem is as you said - "like 1 student studies one subject with 2 different teachers" - then the cause can only be the fact that you are assigning multiple teacher_ids to the same course_id in the table teacher_courses.

Note that the situation one teacher can teach multiple courses is a valid one. E.g. in teacher_course is allowed to have the same teacher_id for multiple course_ids.

Upvotes: 1

Related Questions