How to query Boolean columns to return ones with value 0?

[Charles Ndethi]

Hi,

I want to query the table below as to return the titles of the column where the intersection of SessionId and Roomx is 0.The type of the Room is TINYINT - which in mysql represents BOOLEAN

Below is the table:

SessionId    Room1    Room2    Room3
        1          0        1        0  
        2          1        0        1

For the above table for example for SessionId 1 , query should return Room1 and Room3
Help will be much appreciated.
Thanks.

[Rabbit]

You'll need to unpivot the data if MySQL supports that functionality. If not, you'll need to unpivot manually with a union query. Once the data is normalized, you can make the query.

[Charles Ndethi]

Ok.Thanks.
Mysql does not support PIVOT/UNPIVOT.Therefo re i need to do a manual pivot using a union query you said?
This means creating a pivot table?
How should i go about this , for the table above ?

[Rabbit]

You don't create a pivot table. You can just use a query to unpivot each column. Something like

SELECT SessionID, 'Room1' AS RoomID, Room1 AS columnName
FROM Table1

UNION

SELECT SessionID, 'Room2' AS RoomID, Room2 AS columnName
FROM Table1

etc.

[dgreenhouse]

See my posting here:

http://bytes.com/topic/mysql/answers/908466-where-clause-possible-show-columns-command