I'm attempting to write an HQL statement that uses an inner select, but Hibernate's complaining about invalid syntax. I'm fairly certain I've got something wrong, but I can't find information about how to do an inner
select with HQL.

I'm attempting to select object a in two buckets - those with a b.name of 'FOO', and those with either no b record, or with b.name as anything else.
I'm interested in all a objects, and the b.name values of those that meet
the criteria.


I'd like to write something like so




Hibernate says that the ( in 'left outer join (select' is incorrect.

Is this in the reference guide, or elsewhere? My google-fu is lacking today.

Thanks for your help.
- Mark

When writing HQL, you need to think in terms of object relationships. In the content you've provided there is no stated relationship between there 'a' and 'b' classes. If you can clarify their relationship here, we'll better understand your issue.

An idea for your own effort is to work out the actual SQL you want to generate, and work backwards, using your Hibernate mapping files, to arrive at the correct HQL.

dlgrasse

Hi dlgrasse,

try to add child criteria in parent criteria.will give you inner join.But it would return all the child in parent object if any child match with criteria otherwise will return null

dharmendra.pandey's got it right, but I didn't make a very clear example.

Let's say we've got three tables.

A
id
value

B
id
a_id
c_id

C
id
lookup_value (values WANTED, UNWANTED)

Assume A's value is what we really want. C is a lookup table, and B joins the
two in a many-many, represented as a collection between the java versions of A and C in the mapping files.

Let's also say that C has several values for 'lookup_value'. I'm interested in
finding only A's value for lookup_value of 'WANTED' - every other lookup value in C should be considered 'NULL'. So, what I really want is a C' table
that only has 2 values for 'lookup_value', WANTED or NULL.

If I put that restriction down in the SQL where clause eg.

select a.id, a.value
from a
,join b on a.id = b.a_id
, left outer join c on b.c_id = c.id
where c.lookup_value in (WANTED, NULL)

I get only the a records that have a c.lookup_value of WANTED or NULL.
If an a record has only a lookup_value of UNWANTED, the record goes missing.

If instead we were to manually create a temporary table of C that had
only the WANTED lookup records in it, we could join it with an outer join
and effectively create two buckets - WANTED and null.

insert into c_wanted
(select c.* where c.lookup_value = 'WANTED');

select a.id, a.value
from a
,join b on a.id = b.a_id
, left outer join c_wanted on b.c_id = c.id;

(not actually attempted)

What I'd like to do is stick the selection on c as an inner-join clause, so the result of that inner query is treated like a temporary table, before getting processed in the where clause.

select a.id, a.value
from a
,join b on a.id = b.a_id
, left outer join (select c.* where c.lookup_value = 'WANTED');

Something along those lines.

Thanks again for everybody's help.