How to use Case after using Inner Join ON

Question

How do I use a case expression after the last inner join ON?

Create View v_classroom
AS
SELECT A.roomcode,A.roomname,A.deviceID,
B.occupancy,B.Temperature,B.localtime,
C.classend,C.classstart,C.subject
From devicemessage b
inner join device A ON b.deviceID =A.deviceId
inner join classtimetable C on A.roomcode= c.roomid
//i want to use case statement here 

Answer 1

It depends on what you want the case statement to do.

For example, say you want to return a value based on the occupancy. In this case, your case statement would need to be part of the select clause.

Create View v_classroom
AS
SELECT 
     A.roomcode
    ,A.roomname
    ,A.deviceID
    ,B.occupancy
    ,case     -- This returns a new column 'Occupancy_result' with the case statement result
        when occupancy >= 5 then 'ROOM FULL'
        when occupancy = 0 then 'ROOM EMPTY'
        else 'ROOM PARTIALLY FULL'
        end as Occupancy_Result 
    ,B.Temperature
    ,B.localtime
    ,C.classend
    ,C.classstart
    ,C.subject
From devicemessage b
    inner join device A 
        ON b.deviceID = A.deviceId
    inner join classtimetable C 
        on A.roomcode = c.roomid

If you want to filter your results based on their values, a better choice would be a where statement after the join clauses.

Create View v_classroom
AS
SELECT 
     A.roomcode
    ,A.roomname
    ,A.deviceID
    ,B.occupancy
    ,B.Temperature
    ,B.localtime
    ,C.classend
    ,C.classstart
    ,C.subject
From devicemessage b
    inner join device A 
        ON b.deviceID = A.deviceId
    inner join classtimetable C 
        on A.roomcode = c.
where occupancy <= 5    -- This filters out any results that do not meet the condition

If neither of these solutions quite meet your needs on their own, a combination of the two may be the answer. You can use a case statement in the select clause and then filter the resulting values in the where statement.