title: LEFT JOIN in Hibernate Query Language
body: <p>I am trying to do a LEFT JOIN in Hibernate Query Language, in MySQL I can do this as follows:</p>
  
  <blockquote>
    <p>select * from day_timetable_timeslots t LEFT JOIN golfnine_date_time_entity d ON d.start_time = t.start_time</p>
  </blockquote>
  
  <p>In my table day_timetable_timeslots I have many time intervals for the whole day with 15 minute increments. eg. 00:00:00, 00:15:00, 00:30:00, ... to end of day.
  So it shows me all the time intervals with any matching golfnine_date_time_entity, but I cannot seem to work out how to do this in Hibernate Query Language.</p>
  
  <p>Well I can do the LEFT JOIN by the following HQL.</p>
  
  <blockquote>
    <p>select o.id, book.id from DayTimetableTimeslots o left outer join o.bookings book where o.id > 0 </p>
  </blockquote>
  
  <p>I don't know why I must put the o.id > 0 there, but it works.</p>
  
  <p>However, I want to only select book.id's where the booking has a where condition.
  I tried:</p>
  
  <blockquote>
    <p>select o.id, book.id from DayTimetableTimeslots o left outer join o.bookings book where o.id > 0 and book.dateTime.startDate > '2010-01-01' </p>
  </blockquote>
  
  <p>But this doesn't work correctly, it shows only some of the DayTimetableTimeslots, but not all of them, as it is supposed to do so.</p>
  
  <p>I basically want to do this mysql query in HQL.</p>
  
  <p>select t.id as start_time_sequence, t.start_time as all_start_time, d.* from day_timetable_timeslots t LEFT JOIN golfnine_date_time_entity d ON d.start_time = t.start_time AND t.customer_id = d.customer_id AND d.start_date = '2010-01-24'
   WHERE t.customer_id = 11 </p>
  
  <p>Thanks, Philip</p>
  
  <hr>
  
  <p>In mysql I can do the following and it shows me all bookings against their start time. All start times are stored in day_timetable_timeslots.</p>
  
  <p>select t.start_time, d.id from day_timetable_timeslots t LEFT JOIN golfnine_date_time_entity d ON d.start_time = t.start_time</p>
  
  <pre><code>'00:00:00', NULL
  '00:15:00', NULL
  '00:30:00', NULL
  '00:45:00', NULL
  '01:00:00', '443'
  '01:15:00', NULL
  '01:30:00', NULL
  '01:45:00', NULL
  '02:00:00', '444'
  </code></pre>
  
  <p>... for the whole day, it matches up the ids of the golfnine_date_time_entity with the times in day_timetable_timeslots.</p>
  
  <p>The good thing about this mysql query is I can set some criteria on the booking, eg.</p>
  
  <p>select t.id, t.start_time, d.id from day_timetable_timeslots t LEFT JOIN golfnine_date_time_entity d ON d.start_time = t.start_time AND t.customer_id = d.customer_id AND d.start_date = '2010-01-24' WHERE t.customer_id = 11</p>
  
  <p>I get</p>
  
  <pre><code>... lots of data then
  '31', '07:15:00', NULL
  '32', '07:30:00', NULL
  '33', '07:45:00', NULL
  '34', '08:00:00', '501'
  '35', '08:15:00', NULL
  '36', '08:30:00', NULL
  '37', '08:45:00', NULL
  '38', '09:00:00', NULL
  '39', '09:15:00', NULL
  ... lots more data
  </code></pre>
  
  <p>So its only showing the booking on that date specified and the customer id.</p>
  
  <p>Its so hard to do this in HQL...</p>
  
  <hr>
  
  <p>This is the join I want in HQL.</p>
  
  <p>select o.id, b.id from DayTimetableTimeslots o, LegacyDateTimeEntity b where b.customer.id = 11 and b.startDate = '2010-02-07' and o.startTime = b.startTime</p>
  
  <p>Gives this SQL.</p>
  
  <pre><code>select
      daytimetab0_.id as col_0_0_,
      legacydate1_.id as col_1_0_ 
  from
      day_timetable_timeslots daytimetab0_,
      golfnine_date_time_entity legacydate1_ 
  where
      legacydate1_.customer_id=11 
      and legacydate1_.start_date='2010-02-07' 
      and daytimetab0_.start_time=legacydate1_.start_time
  </code></pre>
  
  <p>But - it only returns 1 row because only one golfnine_date_time_entity matches, I want all of the day_timetable_timeslots rows returned.</p>
  
  <p>So I tried in HQL.</p>
  
  <p>select o.id, o.bookings.size from DayTimetableTimeslots o left join o.bookings book left join book.dateTime dt with dt.customer.id = 11 and dt.startDate = '2010-02-29' where 1 = 1</p>
  
  <p>Which sadly seems to ignore the with expression.</p>
  
  <p>It returns this SQL.</p>
  
  <pre><code>select
      daytimetab0_.id as col_0_0_,
      (select
          count(bookings3_.timeslot_id) 
      from
          golfnine_booking bookings3_ 
      where
          daytimetab0_.id=bookings3_.timeslot_id) as col_1_0_ 
  from
      day_timetable_timeslots daytimetab0_ 
  left outer join
      golfnine_booking bookings1_ 
          on daytimetab0_.id=bookings1_.timeslot_id 
  left outer join
      golfnine_date_time_entity legacydate2_ 
          on bookings1_.date_time_id=legacydate2_.id 
          and (
              legacydate2_.customer_id=11 
              and legacydate2_.start_date='2010-02-29'
          ) 
  where
      1=1
  </code></pre>
  
  <p>Which just joins all matching relationships between tables and ignores
               legacydate2_.customer_id=11 
               and legacydate2_.start_date='2010-02-29'</p>
  
  <hr>
  
  <p>I found that this seems to work. Note I am referencing dt2 which is in the with clause.</p>
  
  <p>select distinct o.startTime, dt2.id, book.uniqueDateTimeResource from DayTimetableTimeslots o left join o.bookings book with book.deleted = 0 left join book.dateTime dt2 with dt2.startDate = '2010-01-19' and dt2.deleted = 0</p>