开发者

returning the name of the column with the boolean value 0

开发者 https://www.devze.com 2023-02-08 23:44 出处:网络
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 BO

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.


It looks to me like a table in need of a redesign. Judging by the column names, the "objects" (rows) you should be dealing with are "Events": An Event happens in a Room, during a section. With that setup, your table would look like:

session_id room_id
1          2
2          1
2          3

Now there's still not a query that'll give you the rooms that aren't in use during a given session, but it's easy to find the ones that are:

SELECT room_id FROM events WHERE session_id = <whatever>

And it's easy to combine that with a list of all possible rooms to get the information you need.

Hope this helps!

PS: If you have a rooms table as well (I wouldn't add one just for this, but if you happened to have one), it gets even easier:

SELECT id FROM rooms WHERE NOT EXISTS (SELECT * FROM events WHERE room_id = id AND session_id = <whatever>)


SELECT column_name FROM information_schema.columns WHERE table_name = 'tabel Name' 
0

精彩评论

暂无评论...
验证码 换一张
取 消