title: Union all where date clause
body: <p>My Query</p>
  
  <pre><code>SELECT RoomType, date FROM Superior WHERE (date &gt;= '2012-06-26'AND date &lt; '2012-06-30')AND (Avail &gt; '0')
  UNION
  SELECT RoomType, date FROM Deluxe WHERE (date &gt;= '2012-06-26'AND date &lt; '2012-06-30')AND (Avail &gt; '0')
  UNION
  SELECT RoomType, date FROM SuperDeluxe WHERE (date &gt;= '2012-06-26'AND date &lt; '2012-06-30')AND (Avail &gt; '0')
  ORDER BY date
  HAVING COUNT(date, INTERVAL 3 DAY) 
  </code></pre>
  
  <p>Getting syntax error near '<code>HAVING COUNT(date, INTERVAL 3 DAY)</code></p>
  
  <p>My goal is to get results for room type with dates and Avail greater than 3 days</p>
  
  <p>** In Table for Superior and Deluxe for date 2012-06-29 Avail=0, for SuperDeluxe all date is Avail</p>
  
  <p>In this case my result should only display SuperDeluxe as Availability but</p>
  
  <pre><code>**RoomType**    **date**
  Superior        2012-06-26
  Superior        2012-06-27
  Superior        2012-06-28
  Deluxe          2012-06-26
  Deluxe          2012-06-27
  Deluxe          2012-06-28
  SuperDeluxe     2012-06-26
  SuperDeluxe     2012-06-27
  SuperDeluxe     2012-06-28
  SuperDeluxe     2012-06-29
  </code></pre>
  
  <p>Yes it is MySQL, I am just trying to proof to myself that i can get this done helping my Uncle. As you know I am not a seasonal just a beginner. You guessed right, all 3 tables are ambiguous. I have another field Avail which keep track of number of rooms available. As a booking is complete, it auto calculate available rooms. </p>
  
  <p>My current queries are using option selection of Room type which can only generate availability for a room type for each query. With the above, I planned to have 1 query and generate any room type that is available in the date range. The problem I have when I use the above union query, the result I get also include one of the date in the range that availability is = 0  </p>
  
  <p>I was trying something like this</p>
  
  <pre><code>SELECT RoomType, COUNT( date )
  FROM Superior
  WHERE date &gt;= '2012-06-26'
    AND date &lt; '2012-06-30'
    AND Avail &gt; '0'
  UNION
  SELECT RoomType, COUNT( date )
  FROM Deluxe
  WHERE date &gt;= '2012-06-26'
    AND date &lt; '2012-06-30'
    AND Avail &gt; '0'
  UNION
  SELECT RoomType, COUNT( date )
  FROM SuperDeluxe
  WHERE date &gt;= '2012-06-26'
    AND date &lt; '2012-06-30'
    AND Avail &gt; '0'
  GROUP BY date
  HAVING COUNT( 4 )
  </code></pre>
  
  <p>Looks like i am getting closer. I tried MAX( date ) it gives date that has 4 rows but it still displaying 1 row of the room type that Avail is > 0</p>
  
  <p>I also tried HAVING COUNT( DISTINCT date ) =4
  Results still display field date with rows not equal to 4</p>