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
|