query from 5 joined tables

Question

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?

Hey. Will it be complicated for you to provide data example here http://sqlfiddle.com/#!9/6479c9/1 to reproduce the problem? Query looks OK at a first glance. Also, what version of MySQL you use? — Evgeny Semionov, Oct 22 '17 at 05:48
Might be shooting in the dark, but can you try this version of the query: 'SELECT s.name, c.name, t.name FROM student s, student_course sc, course c, teacher_course tc, teacher t WHERE s.id = sc.student_id AND sc.course_id = c.id AND c.id = tc.course_id AND tc.teacher_id = t.id' Technically it should do the same, but... who knows. — Evgeny Semionov, Oct 22 '17 at 06:08

score 1 · Answer 1 · 2017-10-22T06:23:16.130

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.

score 1 · Accepted Answer · answered Oct 22 '17 at 08:24

1

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 :

answered Oct 22 '17 at 08:24

Akankha Ahmed

121
9

thanks you! this works, turns out i only had to reverse the PK and FK. whether i left or inner join, i still get the same results. can u explain me little more about this. i thought it doesnt make any different whether u put the FK or PK first. – m5kev4n Oct 22 '17 at 17:35
also, why the result is duplicated. Can u tell me how to not show duplicated results? – m5kev4n Oct 22 '17 at 17:36
@AcousticMike there no duplicate value. yes, 2 students can take one course which can take by the same teacher.for example, 1st 2 column "oren" and "Nathaniel" takes the same course which is taken by Jacob Harteman.That why is at teacher and course_name are duplicate entry . – Akankha Ahmed Oct 23 '17 at 04:13
However, if you want to remove duplicate entry you can use "distinct " after you select query. – Akankha Ahmed Oct 23 '17 at 04:16

query from 5 joined tables

2 Answers2