I am doing a SQL query shown below:

SELECT room.roomid,
CASE 
    when state=1 then 'clear'
    when date between start-date and end-date then 'clear'
    else 'unclear'
END as state
FROM room
join clean
on room.roomid=clean.roomid

Table "Room":

Table "Clean":

Current Output:

Expected Output:

I know that the room is clear at first, because it has been cleared during the 232 clean, but the case is adding another line because the 233 clean is not covering the date of the room state.

How can I say to the CASE that it can only choose when or else and not both please? Or how can I delete the unclear line if I see that the same roomid is clear?

CodePudding user response：

The problem of duplicate rows comes with the JOIN operation, as long as the one row "room.RoomId = 2" is matched with both two rows of "Clean.RoomId".

You can circumvent this problem by forcing the values from the "Clean" table to have dates in your required range.

Then you can simplify the check in the CASE statement, assigning "state = clear" only to those rows with "room.State = 1". Any row that is not assigned a "state" value to (either "room.State = 0" or non satisfying the additional JOIN condition on dates) will be assigned "state = unclear" by COALESCE function.

SELECT room.roomid,
       COALESCE(CASE WHEN State = 1 THEN 'clear' END, 'unclear') AS state
FROM      room 
LEFT JOIN clean
       ON room.RoomId = clean.RoomId
      AND room.date_ BETWEEN Start_Date AND End_Date
ORDER BY RoomId

Check the demo here.