CODEWITHSUNDEEP
mysql
pocpoc
pocpoc

Reputation: 47

Condition with linked tables in MySQL

I'm trying to make a report from my MySQL database.

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 |
|------------|----------------|
| 101        | no             |
| 102        | in-house       |
| 103        | in-house       |
| 104        | no             |
| 105        | in-house       |
|------------|----------------|

I put a SQLFidde here : http://sqlfiddle.com/#!9/3bb474

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
FROM ___Rooms
INNER JOIN ___Bookings
    ON ___Rooms.ROO_id = ___Bookings.BOO_RoomId

Upvotes: 1

Views: 43

Answers (1)

Ankur Patel
Ankur Patel

Reputation: 1433

You seem to have put the < and > conditions in the wrong place. Also, you need to use the LEFT JOIN instead of INNER JOIN. Please see the corrected query below:

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

SQL FIDDLE DEMO

Upvotes: 1

Related Questions