Hibernate version:3.1.2

Name and version of the database you are using:Microsoft SQL Server 2000

I have 2 tables: Parent & Children.
Parent structure: id(int), name(string). Primary key: id.
Children structure: parentid(int), type(int), name(string). Primary key: parentid, type.
"Children.parentid" is related to "Parent.id".

I can use SQL to left join a certain "type" of "Children": "SELECT * FROM Parent LEFT OUTER JOIN Children ON Parent.id=Children.parentid AND Children.type=?" or "SELECT * FROM Parent LEFT OUTER JOIN (SELECT * FROM Children WHERE type=?) ON Parent.id=Children.parentid".

How to use HQL to do the same thing? It is wrong if we write HQL as "from Parent left join Parent.children Children where Children.type is null or Children.type=:type". It is the same as the SQL "SELECT * FROM Parent LEFT OUTER JOIN Children ON Parent.id=Children.parentid WHERE Children.type IS NULL OR Children.type=:type". The HQL doesn't equals the SQL above.

Hibernate also doesn't support such HQLs: "from Parent left join (select Children from Parent.children as Children where Children.type=:type)" & "from Parent left join Parent.children as Children on Children.type=:type".

How to use HQL to do the same thing as the SQL metioned above?

THANKS A LOT!

Parent:
id name
1 a
2 b

Children:
parentid type name
1 1 a
1 2 b
2 1 c

Inner join:
id Parent.name parentid type Children.name
1 a 1 1 a
1 a 1 2 b
2 b 2 1 c

Left join(同Inner join，因为Children中parentid有1和2):
id Parent.name parentid type Children.name
1 a 1 1 a
1 a 1 2 b
2 b 2 1 c

Right join(同Inner join，因为Children中parentid有1和2):
parentid type Children.name id Parent.name
1 1 a 1 a
1 2 b 1 a
2 1 c 2 b

Left join并且在join之前判断type=2:
SELECT * FROM Parent LEFT OUTER JOIN Children ON Parent.id=Children.parentid AND Children.type=2:
id Parent.name parentid type Children.name
1 a 1 2 b
2 b null null null

from Parent left join Parent.children Children where Children.type is null or Children.type=2:
(少了上面null的一组，原因是这个HQL从上面的Left join中取数据之后作type=2的判断，Left join中没有null这组)
id Parent.name parentid type Children.name
1 a 1 2 b

from Children right join Children.parent Parent where Children.type is null or Children.type=2:
(同样少了上面null的一组，原因是这个HQL从上面的Right join中取数据之后作type=2的判断，Right join中没有null这组)
parentid type Children.name id Parent.name
1 2 b 1 a