left join with empty rows from right table mysql

Question

I have restaurants and orders tables, in orders table I have restaurant_id, status and date fields - for each day I save one row in orders table. If for some day there is no order - it means the is no row for that day in orders table.

I want to show on the calendar the data for the current month for each restaurant, according to these 2 separate conditions.

1) in first case show only those restaurants that have at least one free 
day during this month(which means for this month at least one date is missing in orders table).

2) in second case show only those restaurants that are free for today 
(which means there is no row for today in orders table)

for both cases, if the condition is satisfied, I should fetch all the orders for the current month - this is the tricky part.

The usual anti-join with left, or inner join do not give the desired result.

Thanks.

edit

outputs should be like this

1) http://img826.imageshack.us/img826/3114/e6zt.png

2) http://img13.imageshack.us/img13/6397/44l0.png

@PraveenPrasannan, do the images give some insight what I want to achieve ? Thanks — dav
– dav, Commented Aug 21, 2013 at 16:12

GarethD · Accepted Answer · 2013-08-21 16:26:06Z

This is all the listings for this month for all restaurants that are free today:

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
        LEFT JOIN
        (   SELECT  DISTINCT o2.Restaurant_ID
            FROM    orders o2
            WHERE   o2.date = DATE(CURRENT_TIMESTAMP)
        ) o2
            ON r.id = o2.restaurant_id
WHERE   o.Date >= DATE_FORMAT(CURRENT_TIMESTAMP ,'%Y-%m-01')
AND     o.Date <= DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01')
AND     o2.Restaurant_ID IS NULL;

This simply gets all the restaurants with bookings today (subquery o2), then excludes these restaurants:

AND     o2.Restaurant_ID IS NULL;

This is all the listings for this month for all restaurants that have at least one free day this month:

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  o2.Restaurant_ID
            FROM    orders o2
            WHERE   o2.Date >= DATE_FORMAT(CURRENT_TIMESTAMP ,'%Y-%m-01')
            AND     o2.Date <= DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01')
            GROUP BY o2.Restaurant_ID
            HAVING COUNT(DISTINCT o2.Date) < DAY(DATE_ADD(DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -1 DAY))
        ) o2
            ON r.id = o2.restaurant_id
WHERE   o.Date >= DATE_FORMAT(CURRENT_TIMESTAMP ,'%Y-%m-01')
AND     o.Date <= DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01');

The trick is to get the number of days in this month:

DAY(DATE_ADD(DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -1 DAY))

Then limit the results to restaurant_id's that have less bookings than this:

HAVING COUNT(DISTINCT o2.Date) < DAY(DATE_ADD(DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -1 DAY))

Example of Both on SQL Fiddle

Collectives™ on Stack Overflow

left join with empty rows from right table mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related