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'
精彩评论