left join with at least one row with condition from right mysql

Question

I have 2 tables, restaurants and orders, orders table have restaurant_id, status and date fields, each day is a separate line in orders table.

I need to filter the restaurants and show only the restaurants that have at least one reservation for some date range, but I have to show it on a calendar, it should look like a list of restaurants with information.

Smth like this

So, it means that if at least one day with reserved status condition is satisfied in that date range, all the orders for that date range for that restaurant should be fetched as well.

I can not make a usual inner join,

SELECT r.`id`, r.`name`, o.`date`, o.`status`
FROM restaurants r
INNER JOIN orders o ON r.id = o.restaurant_id AND o.date BETWEEN '2013-08-10' AND '2013-08-31'
AND status = 'reserved' 

because, in this case, for example, I will not have the order information for August 31, for restaurant2, because though it is in date rage from 10 - 31, but its status is 'closed'. So, I am thinking to make a left join like this

SELECT r.`id`, r.`name`, o.`date`, o.`status`,  o.`id` order_id
FROM restaurants r
LEFT JOIN orders o ON r.id = o.restaurant_id AND
o.date BETWEEN '2013-08-10' AND '2013-08-31' 
WHERE o.`id` IS NOT NULL 

But I need to also add one condition to ensure that there is at least one row with order's status = 'reserved', I tried to add where clause like COUNT(o.status = 1) > 0 but it gives an error.

THanks

Answer 1

I think the best way to achieve this (assuming things like 'N/A' and 'reserved' are stored in the orders table is to join to all orders in the first instance, and then use a second join to limit it to only restaurants that have a reservation within that period:

SELECT  r.`id`, r.`name`, o.`date`, o.`status`,  o.`id` order_id
FROM    restaurants r
        INNER JOIN orders o 
            ON r.id = o.restaurant_id
            AND o.date BETWEEN '2013-08-10' AND '2013-08-31' 
        INNER JOIN
        (   SELECT  DISTINCT o2.Restaurant_ID
            FROM    orders o2
            WHERE   o2.date BETWEEN '2013-08-10' AND '2013-08-31' 
            AND     o2.Status = 'Reserved'
        ) o2
            ON r.id = o2.restaurant_id;

Example on SQL Fiddle