HQL and LEFT OUTER JOIN syntax

p.lanco · **Joined:** Fri Jan 30, 2009 5:06 am **Posts:** 3

Hi

In HQL, you can use the syntax

Code:

SELECT new MyJoinEntity(c1.CityId, c1.CityName, c1.ZipCode, c2.CountryName, c3.ContinentName)
FROM City c1, Country c2, Continent c3
WHERE c1.CityCountry = c2.CountryId
AND c2.CountryContinent = c3.ContinentId

MyJoinEntity class is a "view" and I don't need a hibernate mapping file
I use <import class="MyLibrary.MyJoinEntity"/>
I don't need (and don't want) to describe the join conditions in hbm file

MyJoinEntity class has a constructor with the same signature as the HQL query

Code:

public MyJoinEntity(int cityId, string cityName, string zipCode, string countryName, string continentName)
{
   this.CityId = cityId;
   this.CityName = cityName;
   this.ZipCode = zipCode;
   this.CountryName = countryName;
   this.ContinentName = continentName;
}

I can get a list of MyJoinEntity

Code:

IList<MyJoinEntity> lst = query.List<MyJoinEntity>();

But sometimes in the database, City.CityCountry is null

I tried to use a LEFT OUTER JOIN but can't find the correct syntax ang get an error

outer or full join must be followed by path expression

In SQL, you have to use ON c1.CityCountry = c2.CountryId, but how in HQL ?

Thanks for help

wolli · **Posted:** Mon Feb 02, 2009 4:17 am

The join syntax in HQL is only for objects that are linked with an association. If you want to join unlinked objects (tables) you have to use a plain SQL query. You approach works unless you only need an inner joing, but not for outer joins.