-2

I have two tables. One is hall which is having following schema. Hall Table

Events Table

One hall can have many events.I want halls which are available between two dates.For example halls between 2016-09-10 and enddate = 2016-09-15. I want all the halls which are not booked for the whole range of dates i.e 10,11,12,13,14,15.

Nishant Dongare
  • 549
  • 3
  • 16
  • Can you show some example data & your exact query & expected results - your 2 scenario are not that enlightening. – PaulF Sep 12 '16 at 10:16
  • Thanks @PaulF for reply. This scenarios are only examples. I mean to say any hall which has atleast one date available in that range. – Nishant Dongare Sep 12 '16 at 10:21
  • I don't even understand what the scenarios are showing - you need to be much clearer. – PaulF Sep 12 '16 at 10:35
  • This is the which i tried `SELECT h.* FROM halls h LEFT JOIN ( SELECT hallid,id FROM EVENTS WHERE EVENTS.eventdate BETWEEN '2016-10-10' AND '2016-10-15' GROUP BY id ) AS e ON h.id = e.hallid WHERE e.id IS NULL` – Nishant Dongare Sep 12 '16 at 10:40

2 Answers2

0

So what's the problem? (:

SELECT 
  * 
FROM events
INNER JOIN halls ON (halls.id = events.hallid)
WHERE 
  events.eventdate >= '2016-09-10' AND events.eventdate <= '2016-09-15';
num8er
  • 18,604
  • 3
  • 43
  • 57
0

create new column in your inner query having value of daypart from eventdate then filter the data using not in based on a new column

ex: select t.* from ( select table1 .hallname,table2.eventdate,DAY(table2.eventdate) as daypart from table1 inner join table2 where eventdate between startdate and enddate) as t where t.daypart not in (select booked eventdate from event table i.e selected by commas)

user3660409
  • 1
  • 2