0

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.

watkib
  • 347
  • 3
  • 11
  • 25

2 Answers2

1

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>)
Xavier Holt
  • 14,471
  • 4
  • 43
  • 56
-2
SELECT column_name FROM information_schema.columns WHERE table_name = 'tabel Name'
Poonam Bhatt
  • 10,154
  • 16
  • 53
  • 72