Lets say we have the following data sets

tbl\_building:

```
id   --       name

1    --       building 1
2    --       building 2

```

tbl\_rooms:

```
id -- building_id -- room_id -- light_status

1   ------   1         -------------        1      -----------     0
2   ------   1         -------------        2      -----------     1
3   ------   1         -------------        3      -----------     0
4   ------   2         -------------        1      -----------     1

```

How would I construct a single sql statement to find out which BUILDINGS have a light switched on in a YES/NO format Whilst grouping by Building name

Idealling I want something like the following:

```
SELECT b.name, if(light_status, 'yes', no) as light_status
FROM tbl_building b
JOIN tbl_rooms r on b.id = r.building_id
group by b.id

```

However, this seems to be random as to which room it will bring back for each buildinh