hello,

I have a question about if its necessary to use a where clause if
two tables are connected by one's primary key and other's foreign key
in their mapping files.

Example:

Department 1 : many Student
student table has foreign key towards department table's primary key.

I use the following query to get data from 2 tables:

select d.name, d.address, s.name from Department as d, Student as s
where d.id = ?

Here, I do not do something like:
s.dept_id = :deptId

since hibernate mappings already specify their one-to-many bi directional association.

I have two questions about this:
-should this work? (I got null data from the above query)
-if a department has a list of students, how to get this list of students
using a query? (For the above query, I use s.name to get a student name,
this will return multiple rows with different student names and same dept)

regards,

select d.name, d.address, s.name from Student as s join s.department as d where d.id = ?


from Student as s join s.department as d where d.id = ?

Thanks Steve.

I am joining 6 tables using the above technique, and I got:

"outer or full join must be followed by path expression"

Is there a different grammar for this?

thanks,

Post the relevant mappings and a description of what you want to try to query.

Thanks, Steve. I used the following query and successfully retrieved data:

select s.department.name s.department.address, s.name, from Student as s
where s.department.name=?

Every department has a Set of students, and I cannot use a separate query to retrieve them, I want to get a Set of students using the same query. I tried this:

select d.name d.address, d.students, from Department as d where d.id=?

but it gave me a "incorrect query syntax " error.

How can we achieve that?

regards,

Should this not be

select d.name, d.address, d.students from Department as d where d.id=?

Your missing a , and added an extra , .

Yes, David, those were my typos, but the query still does not work.

The query you show is correct.

Again, this is my query, thanks for pointing out my typos:

select d.name, d.address, d.students from Department as d where d.id=?

I still got "Incorrect query syntax" error.

I am wondering fi anyone retrieved a Set of children using this kind of query.

Oh. Try elements(d.students) instead of d.students. Read up on the HQL chapter in the documentation.

Great, thanks for the pointer. I now can succesfully retrieve the many end of a one-to-many association.

Now I have a further question: if there is a many-to-may association
between Department and Professor. A department have a Set of professors,
and a professor can have a list of departments, and the association is bidirectional.

If I want to retrieve a list of professors for every department:

select d.name, d.address, elements(d.professors) from Department as d where d.id=?

I got no error but 0 rows returned. Is there a different catch for this?

regards,

Is it possible to do the above?

rgds