有一个旅馆房间表(Room)
roomNo price type旅客表(Guest)
guestNo guestName一个订单表(Booking)
guestNo(FK) roomNo(FK) 我想查询所有房间的信息,如果该房间有旅客则显示旅客的姓名
例如:
roomNo price type
r001 50 type
r002 30 typeguestNo guestName
gt001 nameguestNo roomNo
gt001 r002那得到的结果应该是这样
roomNo price type guestName
r001 50 type
r002 30 type name请问查询语句应该怎么写啊
roomNo price type旅客表(Guest)
guestNo guestName一个订单表(Booking)
guestNo(FK) roomNo(FK) 我想查询所有房间的信息,如果该房间有旅客则显示旅客的姓名
例如:
roomNo price type
r001 50 type
r002 30 typeguestNo guestName
gt001 nameguestNo roomNo
gt001 r002那得到的结果应该是这样
roomNo price type guestName
r001 50 type
r002 30 type name请问查询语句应该怎么写啊
select a.*,isnull(guestname,'')guestname from room a
left join booking c on a.roomno=c.roomno
left join guest b on b.guestno=c.guestno
inner join 与 left join的区别可以看联机丛书. 即会过滤右表不匹配的的行.
from room
left join booking on room.roomno = gooking.roomno
left join guest on booking.guestno = guest.guestno
--------------------------------------------------------------------------
这是根据你的要求来的.所以要用left join如果你要求房间没有预定就不显示,就直接用inner join.(就是三表直接连接.)