Query - Join from 3 tables

Question

I need to do a join query from 3 table Professor , teacher and students. Each professor has a list of teachers and each teacher has a list of student. I am working on a web service which will give me only the professor id and base on that I need to get all the students for this particular professor. So I will need to get list of teachers first ,then from that I can get all students. Anyone can help on this? I don't want to use cascade select. I want to use JOIN FETCH because I'm using jpa.

Table Professor: Professor id , Prof name , Prof address

Table Teacher : Teacher id , Professor id, Teacher name

Table student : Student id, Teacher id, Student name, Student address

Please provide the schema of the tables so that people will be better able to understand your question. — Joyson, Feb 11 '16 at 04:42

score 2 · Accepted Answer · answered Feb 11 '16 at 09:07

Here is the JPA query for your requirement. Note that StudentModel should be mapped to student table. StudentModel should has a property 'teacher' which is 'TeacherModel' type. TeacherModel should have the 'professor' property whose type is 'ProferssorModel' then only this works.

<query name="findStudentsByProfessorID">
    <query-param name="professorId" type="Long" />
        SELECT student 
        FROM StudentModel student
        inner join fetch student.teacher teacher
        inner join fetch teacher.professor professor
        WHERE professor.id = :professorId

</query>

or using sql named query also we can do it.

<sql-query name="findStudentsByProfessorID">
    <query-param name="professorid" type="long" />
    <![CDATA[
        select * from student s join teacher t 
        on s.teacherid = t.teacherid where t.professorid = :professorid;
    ]]>
</sql-query>

Joyson · Answer 2 · 2016-02-11T05:03:24.043

1

As you have not mentioned the table structure the easiest query would be to do the following assuming you have teacherid as foreign key in student table and professorid as foreign key in teacher table:

Select * from student s 
  where s.teacherid in 
     (select t.teacherid from teacher t where t.professorid = 1);

The alternative to using subquery / cascaded select is to use joins.

select * from student s join teacher t 
    on s.teacherid = t.teacherid where t.professorid = 1;

edited Feb 11 '16 at 05:03

answered Feb 11 '16 at 04:42

Joyson

3,025
1
20
34

Yes I have professor id and teacher id as you mentioned. Thanks. But is there any alternative other than cascade select? – Alina Feb 11 '16 at 04:44
1

Any particular reason you are looking to avoid cascade select? – Joyson Feb 11 '16 at 04:53
It does not work in my jpa class. Do you know how to use JOIN fetch ? – Alina Feb 11 '16 at 06:16
I haven't worked on jpa. You could probably try the solution suggested by @Sudhakar – Joyson Feb 11 '16 at 13:01

Query - Join from 3 tables

2 Answers2