MySQL – Assistance in Writing a Query for Specific Problem

MySQL

I need help writing a query to display hotel id, hotel name and hotel type of
hotels which have not taken any orders in the month of 'MAY 19'. Sort the result based on hotel id in ascending order.

Assume we have two table hotel_details and orders.

I have tried the following code

select hotel_id,hotel_name,hotel_type from hotel_details
natural join orders
where order_id is null and monthname(order_date)="May"
group by(hotel_id);

Best Answer

You where on the right track;

natural joins are horrible because a) if there is no table structure, you can't read what they say (hence I'm guessing as the join critiera) b) people change the table structure in the future, and then the query is different.

A left join is required to match hotels where there is no orders. Your where order_id is null could never be true on the same row as monthname(order_date)="May" (if order_id is null, then order_date must be also).

As a left join criteria however, if there is no match, the order side of the result set is empty. Because of this properties we apply the month criteria here.

Grouping by hotel_id was correct.

Finally using having is a criteria based after the aggregation. As NULL isn't counted, the total count for the month must be 0.

select hotel_id,hotel_name,hotel_type
from hotel_details
left join orders
   on hotel_details.hotel_id = orders.hotel_id
   and monthname(order_date)="May"
group by hotel_id
having count(orders.order_id) = 0
order by hotel_id
Related Question