The background is that students can select different classes, set weight and choose a teacher (the choice is A, B, C, D) for the class, one class has four teachers.

Tables are like below

class:
id          integer
name        string
description string

student:
id   integer
name string

student_choice:
id         integer
class_id   integer
student_id integer
weight     decimal
choice     string

teacher:
id           integer
class_id     integer
choice       string
teacher_desc string

What I am trying to do is getting a student's all choices by his/her id, result looks like this

currently I just use student_choice table to find all classes that the student chooses, and find the teacher correspond to student's choice

student.id --> student_choice --> class.id
class.id   student_choice.choice --> teacher

I don't know if it is possiable to implement this with just one SQL query, so I just use multiple queries and process them in my application.

Can anyone tell me what I should do to achieve my purpose with only one SQL query, or just keep the current situation because process them in my application instead of in database may be more efficient?

CodePudding user response：

I might be missing something, but it seems like a simple join between all tables can achieve that.

Something like that:

select sc.class_id, c.name, sc.weight, sc.choice, t.teacher_desc 
from student_choice sc, class c, teacher t
where sc.class_id = c.id
and sc.class_id = t.class_id and sc.choice = t.choice
and sc.student_id = {YOUR_STUDENT_ID}

Or if you prefer inner join syntax:

select sc.class_id, c.name, sc.weight, sc.choice, t.teacher_desc
from student_choice sc
inner join class c on sc.class_id = c.id
inner join teacher t on sc.class_id = t.class_id and sc.choice = t.choice
where sc.student_id = {YOUR_STUDENT_ID}