查询语句求解

数据库中各表是这样的
Hotel ( hotelNo, hotelName, city)
Room ( roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName. guestAddress)
Hotel中包含旅馆的详细资料,hotelNo是主关键字;
Room中包含每个旅馆的房间信息,(roomNo, hotelNo)组成主关键字;
Booking中包含各种预定资料,(hotelNo, guestNo, dateFrom)组成主关键字;
Guest中包含客人的详细资料,guestNo是主关键字;
 
问题是:列出Grosvenor Hotel 中所有房间的详细资料,如果房间已经被占用,还应该包括租用该房间的客人的姓名。
谢谢啦
能详细解释一下 就好了

作者: wind4905564   发布时间: 2011-05-11

SQL code
slect A.*,B.*,C.*,D.*
from room A left join hotel B on A.hotelNo=B.hotelNo left join Booking C on B.hotelNo=C.hotel left join Guest on C.guestNo = D.guestNo
where hotelName='Grosvenor Hotel'

作者: rucypli   发布时间: 2011-05-11

SELECT r.roomNo,
  r.type,
  r.price,
  (SELECT g.guestName FROM Guest g, Booking b WHERE g.guestNo = b.guestNo AND b.hotelNo = r.hotelNo AND b.roomNo = r.roomNo) as guestName
  FROM Room r,
  Hotel h
 WHERE r.hotelNo = h.hotelNo
  AND h.hotelName = 'Grosvenor Hotel'

作者: shine333   发布时间: 2011-05-11