CODEWITHSUNDEEP
mysqlsql
Shaugi
Shaugi

Reputation: 529

ADD statement WHEN CASE SQL

I have table like this: enter image description here

and this is my query for showing avaibility of room 

SELECT 
hotel.nama_hotel,
hotel.kota,
room.id_room,
room.position,
room.price,
room_price.date_start,
room_price.date_end,
room_price.price
FROM room
    JOIN hotel on room.id_hotel = hotel.id_hotel
    JOIN room_price on room.id_room = room_price.id_room
    WHERE 
        (
            room.id_room NOT IN 
            (
            SELECT id_room FROM booking 
                WHERE 
                (
                    booking_start BETWEEN '2018-02-10' AND '2018-02-11' OR 
                    booking_end   BETWEEN '2018-02-10' AND '2018-02-11' OR 
                    '2018-02-10'  BETWEEN booking_start AND booking_end OR
                    '2018-02-11'  BETWEEN booking_start AND booking_end
                ) 
            )
        )
    AND (room.status = 1) 
    AND (hotel.kota="Bandung")
    AND (room.position = "earth")

and i got 2 column of price from table room and table room_price

i need 1 column of price when inserted date is between room_price.date_start AND room_price.date_end and price appear from table room_price when is not on between room_price.date_start AND room_price.date_end the price appear from table room

is that possible to do that ?

EDIT

this the query 

SELECT 
hotel.nama_hotel,
hotel.kota,
room.id_room,
room.position,
room_price.date_start,
room_price.date_end,
    (
        CASE WHEN 
        (
            '2018-02-10' BETWEEN room_price.date_start AND room_price.date_end
        )THEN  
        room.price = room_price.price
        ELSE room.price = room.price END
    ) as price from room 



   JOIN hotel on room.id_hotel = hotel.id_hotel
    JOIN room_price on room.id_room = room_price.id_room
    WHERE 
        (
            room.id_room NOT IN 
            (
            SELECT id_room FROM booking 
                WHERE 
                (
                    booking_start BETWEEN '2018-02-10' AND '2018-02-11' OR 
                    booking_end   BETWEEN '2018-02-10' AND '2018-02-11' OR 
                    '2018-02-10'  BETWEEN booking_start AND booking_end OR
                    '2018-02-11'  BETWEEN booking_start AND booking_end
                ) 
            )
        )
    AND (room.status = 1) 
    AND (hotel.kota="Bandung")
    AND (room.position = "earth")

BUT i dont get the price = room_price.price

Upvotes: 1

Views: 89

Answers (2)

iSR5
iSR5

Reputation: 3498

USE IF() in your SELECT this would save your time. 

hotel.nama_hotel,
hotel.kota,
room.id_room,
room.position,
room_price.date_start,
room_price.date_end,
IF('2018-02-10' BETWEEN room_price.date_start AND room_price.date_end, room_price.price, room.price)
FROM room
INNER JOIN hotel ON room.id_hotel = hotel.id_hotel
INNER JOIN room_price ON room.id_room = room_price.id_room
WHERE 
    room.id_room NOT IN( 
    SELECT id_room 
    FROM booking 
    WHERE 
        booking_start BETWEEN '2018-02-10' AND '2018-02-11' OR 
        booking_end   BETWEEN '2018-02-10' AND '2018-02-11' OR 
        '2018-02-10'  BETWEEN booking_start AND booking_end OR
        '2018-02-11'  BETWEEN booking_start AND booking_end

                    )
AND (room.status = 1) 
AND (hotel.kota="Bandung")
AND (room.position = "earth")

I'm not sure if you need room.id_room NOT IN (...) with it or not, as 

IF('2018-02-10' BETWEEN room_price.date_start AND room_price.date_end, room_price.price, room.price)

Will check the date, if is it true, then will return the price from room_price.price, if is it false, then it will return room.price.

more about it

https://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html

Upvotes: 1

Kobi
Kobi

Reputation: 2524

you miss a , after the 9th line after room.price, when you open a bracket (, SQL think you are calling a function room.price()

Upvotes: 2

Related Questions