I've created a query for MSSQL which is exactly what i need to fulfill my requirement. I need to translate this into HQL. however due to the complexity of the query I'm having some trouble.

The query is:

select distinct P.firstname, P.lastname, S1.name
from dbo.Person P
LEFT OUTER JOIN dbo.Store S1 ON
(P.Id = S1.propertyManagerPersonId OR
P.id = S1.areaManagerPersonId OR
P.id = S1.storeManagerPersonId)



Currently, I have the following HQL:

select distinct p, s from Store s
right outer join s.areaManager as p
right outer join s.propertyManager as p
right outer join s.storeManager as p


The problem with the HQL is that it doesn't return as many rows as the SQL. A Co-worked suggested that the HQL may only joining on s.storeManager, and none of the others.

I have also tried (along with many other variations)

select distinct p, s from Store s
right outer join (s.areaManager OR s.propertyManager OR s.storeManager ) as p



This is the segment of generated SQL regarding the joins:
from Store store0_ right outer join Person person1_ on store0_.areaManagerPersonId=person1_.id right outer join Person person2_ on store0_.propertyManagerPersonId=person2_.id right outer join Person person3_ on store0_.storeManagerPersonId=person3_.id where (person3_.customerId=? )and(person3_.statusId='ENABLED' )

ANY help is greatly appreciated.

Thanks