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

Answer 1

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>