Help with the HQL query

msenin · **Posted:** Sun Aug 07, 2005 12:28 am

I have this SQL query, but I don't know how to do the same in HQL. How to specify multiple join conditions, e.g.

Code:

select * from A left outer join B on B.fk = A.id and B.effectiveDate = systemdate

i.e. The condition is not in WHERE, but in the JOIN.

Here's actual SQL query

Code:

select distinct adGroup.vendorIdString
from AdGroup as adGroup
join Campaign C on C.id = adGroup.campaignId
join VendorKeyword VK on VK.adGroupId = adGroup.id
left outer join KeywordStats S on S.vendorKeywordId = VK.id and (S.startDate = ? and S.endDate = ?)
where C.accountId = ?
     and adGroup.vendorIdString is not null
     and VK.isNegative = 0
group by adGroup.vendorIdString
having count (S.id) = 0

I have 1 Entity per Table mapping, so in Hibernate mapping file, entity names are same as table names. So I am not providing mapping files here.

If exact translation is not possible, I'd appreciate ideas on how to do the same thing efficiently in HQL. It should return those AdGroups that do not have KeywordStats for given dates.

Account has Campaigns, which has AdGroups which has VendorKeywords which has KeywordStats.

Thanks!!!

scesbron · **Joined:** Thu Sep 04, 2003 8:23 am **Posts:** 368

msenin wrote:

Code:

select * from A left outer join B on B.fk = A.id and B.effectiveDate = systemdate

i.e. The condition is not in WHERE, but in the JOIN.

in hql you can have

Code:

 select a from A as a left join a.b as b where (b.effectiveDate is null or b.effectiveDate = systemdate)

or you can use a subquery

Code:

 from A as a  where a in (select a from A as a join a.b as b where b.effectiveDate <> systemdate)

Seb