I have two tables: Student and Class. They have a many-to-many relationship through a join table (Student_Class). I'm using Oracle 10g. The table structures are listed below using SQLPLUS:
The IDs are the PKs and the Student_ID and Class_ID are the FKs.
I can find the classes that Mary is taking with following query:
SQL>select cl.CLASS_NAME from Class cl, Student st, Student_Class sc where st.ID=sc.STUDENT_ID and cl.ID=cs.CLASS_ID and st.STUDENT_NAME like 'Mary%'
CLASS_NAME ------------ Math English Physics
I can find the classes that Mike is taking with following query
SQL>select cl.CLASS_NAME from Class cl, Student st, Student_Class sc where st.ID=sc.STUDENT_ID and cl.ID=cs.CLASS_ID and st.STUDENT_NAME like 'Mike%'
CLASS_NAME ------------ Math English Chemistry
I can find the classes that Mary or Mike are taking with following
SQL>select distinct cl.CLASS_NAME from Class cl, Student st, Student_Class sc where st.ID=sc.STUDENT_ID and cl.ID=cs.CLASS_ID and (st.STUDENT_NAME like 'Mike%' or st.STUDENT_NAME like 'Mary%')
CLASS_NAME ------------ Math English Chemistry Physics
Can anyone help me to wirte a Hibernate HQL or QBE to do above queries (especially the last one)? Thanks.
HQL is just like SQL, except you use Class names and attributes names instead of table and field names. So we can't really post an HQL query for you because we don't know your class and attribute names that you have mapped to those tables. But besides the "distinct" in that last query, your HQL will basically looks the exact same, except change the table name to you class name and your fields to the class' attribute.
0
