Duplicate rows after a MySQL query

debugcn 投稿 Dev

user10065337

I have 2 tables:

___Rooms

|--------|------------|
| ROO_Id | ROO_Number |
|--------|------------|
| 1      | 101        |
| 2      | 102        |
| 3      | 103        |
| 4      | 104        |
| 5      | 105        |
|--------|------------|

___Bookings

|--------|------------|------------|------------|-------------------|
| BOO_Id | BOO_RoomId | BOO_DateCI | BOO_DateCO | BOO_ArrivalStatus |
|--------|------------|------------|------------|-------------------|
| 1      | 1          | 2018-07-03 | 2018-07-05 | checkout          |
| 2      | 2          | 2018-07-16 | 2018-07-17 | checkin           |
| 3      | 3          | 2018-07-14 | 2018-07-16 | none              | 
| 4      | 5          | 2018-07-14 | 2018-07-18 | checkin           | 
|--------|------------|------------|------------|-------------------|

My goal is to have the following report:

The date of the report is today : 2018-07-16.

|------------|----------------|-------------------|
| ROO_Number | BOO_LiveStatus | BOO_ArrivalStatus |
|------------|----------------|-------------------|
| 101        | no             | checkout          |
| 102        | in-house       | checkin           |
| 103        | in-house       | none              |
| 104        | no             | 0                 |
| 105        | in-house       | checkin           |
|------------|----------------|-------------------|

I put a SQLFidde here : http://sqlfiddle.com/#!9/854079/5

Actually, I'm very near but I have a little problem.

In my report I need one row per room in ___Rooms table. But actually, I have more rows (2 for #102).

(1, 2, '2018-07-13', '2018-07-15', 'checkout') should not be display because the today date is not between 2018-07-13 and 2018-07-15.

My last try was this one:

SELECT 
    ROO_Number, 
    IF(BOO_DateCI <= '2018-07-16' AND BOO_DateCO >= '2018-07-16', 'in-house', 'no')
        AS BOO_LiveStatus,
    IFNULL(BOO_ArrivalStatus, '0') 
        AS BOO_ArrivalStatus
FROM ___Rooms
LEFT JOIN ___Bookings
    ON ___Rooms.ROO_id = ___Bookings.BOO_RoomId
ORDER BY ROO_Number

Could you please help me please ?

Thanks a lot.

Barmar

Add the date check to the ON clause.

SELECT 
    ROO_Number, 
    IF(BOO_DateCI <= '2018-07-16' AND BOO_DateCO >= '2018-07-16', 'in-house', 'no')
        AS BOO_LiveStatus,
    IFNULL(BOO_ArrivalStatus, '0') 
        AS BOO_ArrivalStatus
FROM ___Rooms
LEFT JOIN ___Bookings
    ON ___Rooms.ROO_id = ___Bookings.BOO_RoomId 
        AND CURDATE() BETWEEN ___Bookings.BOO_DateCI AND ___Bookings.BOO_DateCO
ORDER BY 
  ROO_Number

You need to do it in ON rather than WHERE because putting it in WHERE will filter out all the rooms with no bookings at all (since the columns will be NULL).

SqlFiddle

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]