php - mysql选择房间类型问题

Question

我有如下两张表，对于每家酒店会有房间类型并且对于每个房间类型会有房间..因此，在每家酒店下，我想显示分配给他们的房间类型以及分配给该房间类型的所有房间。

房型表

ID  || NAME || hotel_id ||
1   || a    ||    33    ||
2   || b    ||    55    ||
3   || c    ||    33    ||

房间表

ROOM_ID || ROOM_TYPE_ID  || hotel_id || checkin      || checkout     ||
1       ||   1           ||    33    || 2015-02-04   || 2015-02-08   ||
2       ||   2           ||    55    || 2015-02-24   || 2015-02-28   ||
3       ||   3           ||    33    || 2015-04-14   || 2015-03-18   ||
4       ||   3           ||    33    || 2015-04-18   || 2015-03-28   ||

结果应该是这样的

Hotel id = 33

room types = a,c
rooms      = 1,2

我尝试了下面的连接，但不是我想要的

SELECT r.* FROM rooms r JOIN room_types rt ON rt.room_type_id = r.room_id WHERE r.hotel_id  = '" . (int)$hotel_id . "'

*结果应该如何**

[ http://i.stack.imgur.com/xKXcp.png][1]

谢谢!

Answer 1

对不起，我手头没有sql server，所以你可能需要稍微调试一下:

"SELECT COUNT(r.room_id) as rooms , rt.*
FROM rooms r 
INNER JOIN (
  SELECT * 
  FROM room_types 
  WHERE hotel_id  = 33
    AND name in ('a','c') ) 
AS rt 
ON rt.id = r.room_type_id 
WHERE r.room_id in ( 1, 2 )
GROUP BY rt.id"