Mysql 查询按日期搜索 [英] Mysql Query Search By Dates

问题描述

我尝试创建一个查询，显示入住和退房日期之间的可用酒店.同样，当我预订特定房间时，我将他们的预订列设置为 1.这意味着，当退房日期在现实生活中时，触发函数会将预订的列设置为 0.

I try to create a query which shows available hotels between checkin and checkout dates. Also when I make a reservation for specific rooms, I set their booking column as 1. That means, when the checkout date comes in real life, a trigger function will set that booked column as 0.

如果一个酒店的所有房间都被预订了(booked=1)并且这些房间的入住-退房日期是特定的日期(入住-退房输入)，那么不要把那家酒店放在列表中.我的查询没有显示我想要的结果.

If all rooms of a hotel are booked(booked=1) and checkin-checkout dates of those rooms are specific date(checkin-checkout inputs), then don't put that hotel in the list. My query doesn't show the result that I want.

查询:输入:国家(州)、签入和签出.

Query: Inputs: Country(state), checkin and checkout.

SELECT DISTINCT a.* FROM accommodation a INNER JOIN cb_states s ON a.state = s.id
INNER JOIN accommodation_rooms ar ON a.id = ar.accommodation
WHERE state = 1 AND a.id NOT IN
(
    SELECT 1 FROM booking b
    WHERE
    (
        (b.arrival_date BETWEEN '2017-11-16' AND '2018-03-16') 
        OR
        (b.departure_date BETWEEN '2017-11-16' AND '2018-03-16')
    )
)

当我运行查询时，无论日期如何，它总是显示所有酒店.如果我写 ... WHERE ar.booked = 0 AND state = 1 AND a.id NOT IN...，那么它不会显示住宿 ID 13，但它也不会显示当我更改日期时.

When I run the query, it always shows all hotels no matter dates. If I write ... WHERE ar.booked = 0 AND state = 1 AND a.id NOT IN..., then it doesn't show accommodation id 13, but it doesn't show either when I change dates.

住宿表:

accommodation_rooms 表:

accommodation_rooms table:

预订表:

booked_rooms table:(有外键和booking table)

booked_rooms table:(has foreign key with booking table)

推荐答案

您的查询有多个问题:

1. NOT IN 列表有 SELECT 1.这并没有给 NOT IN 列表带来很大的变化.
2. 你的重叠逻辑是错误的.
3. 我认为您需要将住宿房间与预订的住宿房间联系起来.

1. The NOT IN list has SELECT 1. That doesn't give a great variety to the NOT IN list.
2. Your logic for overlaps is wrong.
3. I think you need to connect accommodation rooms to booked accommodation rooms.

所以:

SELECT DISTINCT a.*
FROM accommodation a INNER JOIN
     cb_states s
     ON a.state = s.id INNER JOIN
     accommodation_rooms ar
     ON a.id = ar.accommodation
WHERE s.state = 1 AND
      a.id NOT IN (SELECT br.accommodation_room
                   FROM booking b JOIN
                        booked_room br
                        ON b.?? = br.??
                   WHERE b.arrival_date <= '2018-03-16' AND
                         b.departure_date >= '2017-11-16' 
                  );

这篇关于Mysql 查询按日期搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！