Hello folks,

I am trying to join two EJB's using EJBQL (with an underlying MySQL data source). The two tables in question are

Machine
- Hostname
- ... unrelated fields ...

and

Location
- Code
- Human readable description

The tables should be LEFT joined on the location code and the first three characters of the machine's hostname. In straight up MySQL the command is:

SELECT * FROM machine m LEFT JOIN location l ON (SUBSTRING(m.`Hostname`, 1,3) = l.`Code`);

When I put a similar thing into EJBQL I get all sorts of errors ranging from "outer or full join must be followed by path expression" to invalid syntax exceptions -- here's what I have tried:

query="SELECT NEW someObj(m, loc) FROM Machine as m " +
"LEFT JOIN FETCH Location as loc " +
"WHERE (SUBSTRING(m.hostname, 1, :length) = loc.code)"

I've also tried using "ON" instead of "WHERE" -- but EJBQL comes back with an unexpected keyword when I use ON...

So -- has anyone succeeded in doing something like this before? The documentation for EJBQL seems to indicate that LEFT join is OK, so I'm not sure what the deal is...

Note: There is no relation between a Machine and a Location, unfortunately I cannot change that

Thanks

Dan

Has anybody found a solution to this problem. I am also facing the similar issue.

I have an unmapped entity but need to join the table using left outer join on a particular column.. How does it works in Hiberante and what version supports it.

Thanks,
YA