I have the problem, that I want to use a left join on two tables. The two tables are 1..1 related. In my case this means left table (routinginformation) has all possible internal ip addresses. In the second one I have all used hosts with their reference to the routing table. And this is clear: not all routings maybe occupied by hosts.

Now on the mysql side I will use the following ansi SQL query:
mysql> select r.internalip, h.hostwinsname from routinginformation as r left join hostinformation as h on r.routingid=h.routingid;

The on clause defines the connection between the two tables. But the hql query creates a wrong sql. As it can be seen below. The on defines >on routinginf0_.routingid=hostinform1_.hostid<.

What can I do to make the generated one correct?

Thanx
Victor

Hibernate version:
2.1

Code between sessionFactory.openSession() and session.close():
session.createQuery("from Routinginformation as routing left join routing.hostinformation as host order by routing.internalip").list();

Name and version of the database you are using:
mysql 4.1 gamma

The generated SQL (show_sql=true):
10:54:45,070 INFO [STDOUT] Hibernate: select routinginf0_.routingid as routingid0_, hostinform1_.hostid as hostid1_, ro
utinginf0_.internalip as internalip0_, routinginf0_.externalip as externalip0_, hostinform1_.hostwinsname as hostwins2_1
_, hostinform1_.hostdnsname as hostdnsn3_1_, hostinform1_.hostmacaddress as hostmaca4_1_, hostinform1_.hostos as hostos1
_, hostinform1_.hostdescription as hostdesc6_1_, hostinform1_.typeid as typeid1_, hostinform1_.routingid as routingid1_,
hostinform1_.userid as userid1_, hostinform1_.statusid as statusid1_ from routinginformation routinginf0_ left outer jo
in hostinformation hostinform1_ on routinginf0_.routingid=hostinform1_.hostid order by routinginf0_.internalip