It took me a few minutes to come up with this SQL:
select cilocs.name, cilocs.region, colocs.station_code, sl.name
from company_locations colocs
inner join companies co on colocs.company_id = co.id
inner join city_locales cilocs on colocs.city_id = cilocs.city_id
inner join locales l on cilocs.locale_id = l.id
left join station_locales sl on (colocs.station_code = sl.station_code and cilocs.locale_id = sl.locale_id)
where co.active = true and l.id = 'en_US'
and upper(cilocs.name) like 'SPA%'
group by cilocs.name, colocs.station_code, cilocs.region, sl.name;

It's been an hour and half and I still haven't figured out how to write this in HQL. Any ideas?

This is where I'm stuck: trying to figure out how to join CompanyLocation and StationLocale without HQL generating two SQL left joins.

This is what I have currently:

left join colocs.station.localizedStation as sl

which creates two left joins when it gets converted to SQL:
left outer join
stations station4_
on companyloc0_.station_code=station4_.code
left outer join
station_locales localizeda5_
on station4_.code=localizeda5_.station_code

This is a waste. How can I have it just generate the following:
left outer join
station_locales localizeda5_
on companyloc0_.station_code=localizeda5_.station_code

I hope this is enough info.

Yeah, I reread this and thought that I could definitely provide more info.

I have CompanyLocation that has a station property as ManyToOne with Station. StationLocale has a station property as ManyToOne with Station. I created a OneToMany relationship in Station that's called localizedStation.

I am writing left join colocs.station.localizedStation as sl and it will join two tables: stations and station_locales. I just want to join station_locales as localizeda5_ on companyloc0_.station_code=localizeda5_.station_code. How do I do this? Must I create some other type of relationship?