Simple HQL/sql query..need assistance?

I have 2 tables (entity) a person entity and a vacation entity. One person can have many vacations.

What I'm trying to do is the following query:

Select all the persons period!, but for the persons who have a vacations, I want to fetch all of the persons vacations which have a condition , lest just say startDate is between something.

Just to be clear no matter the constraints on the vacations I still want to get all the persons.this is my current HQL query:

select distinct p 
from Person as p 
left join fetch p.vacations as v 
where v.startDate between '2011-07-01' and '2011-07-30'

The problem with it is that it constraint my persons an returns only the ones who have a vacation that respects the condition....Can someone help please?

I have no problem if someone can tell m开发者_如何转开发e this in native SQL

UPDATE Resolved in native SQL thanks to @Quasdunk:

SELECT * 
FROM person as p 
LEFT JOIN 
     (SELECT * FROM vacation
      WHERE startDate BETWEEN '2011-08-01' AND '2011-08-30') as v ON v.person_id = p.id

My new problem is how to convert it to an HQL query so I can fetch the vacations for each person, and the persons to be distinct (the conditions remain the same).

---

In native SQL this would be:

SELECT * 
FROM person 
LEFT JOIN (SELECT * 
           FROM vacations 
           WHERE startDate BETWEEN '2011-07-01' AND '2011-07-30')

untested, but you get the idea :)

---

Try the following,

select p.*, v.* from PERSON p LEFT JOIN VACATION v on (p.id = v.p_id) where v.startDate between '2011-07-21' and '2011-07-22'

Hope this works for you.

---

You might want to use functions to achieve it:

select p from Person p 
where p.vacations.size = 0 
or (minelement(p.vacations) > '2011-07-01' 
    and 
    maxelement(p.vacations) < '2011-07-30')

Those dates might be actually a Date type. Depending of the type.

Try

select p from Person p 
where p.vacations.size = 0 
or (p.vacations.startDate > '2011-07-01' 
    and 
    p.vacations.endDate < '2011-07-30')

You should also take a look at here

---

In plain (My)SQL it would look like this:

select distinct p.* from person p
left join vacations v on p.id = v.person_id 
where v.id is null or.v.start_date between '2011-07-01' and '2011-07-30'

In HQL this will probably be (not tested)

select distinct p from Person as p 
left join fetch p.vacations as v 
where v.id is null or  v.startDate between '2011-07-01' and '2011-07-30'

or maybe (I'm not really sure if this will work)

select distinct p from Person as p 
left join fetch p.vacations as v 
where v is null or v.startDate between '2011-07-01' and '2011-07-30'