Hi,

I have the problem with HQL select command

getHibernateTemplate().find("select.....")

I have the main select with 2 tables tender and tender_bid (conected by id columns) and in the where condition is sub-select with the same table as in main select (tender_bid).

the problem is, main-select and sub-select has the same alias for tender_bid table.

HQL command:



generated SQL command:



the problem is, that the first tender_bid and the second (in sub-select) has the same alias tenderbid1_, and generated SQL command is WRONG.

correct SQL command:



thanks

Ivan

Hi Ivan,
I'm not a pro in HQL/Hibernate but I would give a try like this:-
I don't know your data model but I have made assumptions Please read and use it cautiously.

Firstly, modify the query which would get all the minimum priced bids grouped by carrier.

Make sure this query does not return multiple records for the same carrier

select min(tenderbid1_.bid_price),tenderbid1_.id,tenderbid1_.fk_carrier_id,
tenderbid1_.fk_tender_id
from tender_bid tenderbid1_
group by tenderbid1_.fk_carrier_id,tenderbid1_.fk_tender_id,tenderbid1_.id)


And then try to achieve the same I don't know how to but the target SQL that you should be looking for needs to look like this.(This is how I would approach but I could be wrong!)

select
tender0_.id as col_0_0_,
tenderbid1_.id as col_1_0_
from
tender tender0_
left join (select min(tenderbid1_.bid_price),tenderbid1_.id,tenderbid1_.fk_carrier_id,
tenderbid1_.fk_tender_id
from tender_bid tenderbid1_
group by tenderbid1_.fk_carrier_id,tenderbid1_.fk_tender_id,tenderbid1_.id) tenderbid1_ on(tenderbid1_.fk_tender_id=tender0_.id)
where
tenderbid1_.fk_carrier_id=?

Hope this helps,
Srilatha.

I think you can use DetachedCriteria

I faced this problem and solved it with detached criteria. Here's the page.
http://sites.google.com/site/developmen ... atement-wi
You would just replace propertyIn with propertyEq.