Hey guys,
I've got to implement a query performing the following task, I'm sure the query should be clear:



but 'on' seems to be wrong. Why's that? Is there any way to perform this task using HQL?

Example: Table X contains months from 1 .. 13, in Z i've got month's and value's, and I want to get all months from Blah and null if there's no such value in Z .. hmm.. I hope it's clear :) Something like:


Any ideas?

Thanks,
Kornel

If Blah has a mapped reference to ZTable you can do something like that:


If theres no mapped references between Blah and ZTable try something like that:

as far as i know the keyword "on" is not supported here in HQL.
it may make sence, because the on conditions are already described in the mapping associations.

where condition has to be used instead if no mapping is specified.
this may help: http://en.wikipedia.org/wiki/Join_(SQL)#Inner_join

only the keyword with is supported

Thanks a lot guys, but the thing is, I desperately need the null values (no way I do select from x,y where x = y) and there's no relationship ;-)
I'll explain what's going on, why I can't have a relationship: I've got a table with records - month / year / value / category. And I need to get e.g. all values (from all categories) from January 2008 to December 2008, and if there's no such record, I need a zero (or null, or whatever indicating that there's no such value). Of course, I can iterate through the result and insert these zeroes, but a simple join using simple sql would do the trick. I'm fetching quite a lot of such records and insertion while iterating and checking doesn't seem to be any good. Especially, that's a quite rare case, a missing value will not happen that often, so iterating each time through the whole collection, you get the idea.

Any ideas how to get these d*mn zeroes? :) Maybe I'm missing something really basic and simple, this case doesn't seem to be that difficult at all.. Maybe native sql, but I use these queries as a last resort, really.

Regards,
Kornel

your first query missed the outer join syntax. so if you have a mapping this would work.

i see those possibilities:

doing a native SQL query

try to use HQL to solve it

for HQL you may try to get the same result with a correlated sub-query,
but i never tried this in HQL!
read the alternative to outer joins here:
http://en.wikipedia.org/wiki/Join_(SQL)#Inner_join

using also:
http://en.wikipedia.org/wiki/Correlated_subquery

Generally:
i think that the requirement of such queries where:
.) no mapping is modeled and basically reuqired execpt for special quieries.
is given in many applications,
and this problem and its solution may be a good point to introduce into the hibernate documenation itself.