mysql中查询空行,左连接右表mysql中的空行

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

解决方案

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))