从多(4)个表返回记录

Question

我正在尝试编写一个从多个表返回记录的查询。

ConfRooms包含房间列表FIDS_Hardware包含有关房间中硬件的信息ConfBookings包含已预订房间的列表ConfRoomOverride包含有关房间状态的信息，即房间是否设置了覆盖标志

返回"ConfRooms AS c“中的所有记录，其中c.HotelID = 'EXBHX‘。

返回"FIDS_Hardware AS h“中的所有记录，其中r.HotelID = 'EXBHX‘，c.ConfRoomID = h.deviceID。

返回"ConfBookings AS b“中的所有记录，其中HotelID = 'EXBHX‘，c.ConfRoomName = b.RoomName。

返回"ConfRoomOverride AS o“中的所有记录，其中HotelID = 'EXBHX‘，c.ConfRoomNID = b.RoomID

目前，我的查询不是返回"ConfRooms“中的所有记录，而是只返回ConfBookings中状态为”已预订“的房间。

有人能看出我哪里错了吗？

SELECT 
h.messageCount,
h.deviceID, 
c.ConfRoomName, 
r.DisplayMainBoard, 
r.RecordID, 
r.RoomID, 
r.RoomName,
r.RoomBooked,
r.RoomFromDate,
r.RoomToDate,
r.FullBoardRoom, 
COUNT(r.RecordID) AS RC,
r.Override,
r.HotelID,
o.Override AS ConfOverride
FROM
ConfRooms AS c
Left Join FIDS_Hardware AS h ON (c.ConfRoomID = h.deviceID)
Left Join ConfBookings AS r ON (c.ConfRoomName = r.RoomName)
Left Join ConfRoomOverride AS o ON (c.ConfRoomID = o.RoomID)
WHERE c.HotelID = 'EXBHX' 
AND r.HotelID = 'EXBHX'
AND h.hotelID = 'EXBHX'
GROUP BY c.ConfRoomID
ORDER BY h.messageCount DESC,
ConfOverride DESC, 
r.RoomBooked DESC,
c.ConfOrder ASC

Answer 1

试着这样做：

SELECT 
h.messageCount,
h.deviceID, 
c.ConfRoomName, 
r.DisplayMainBoard, 
r.RecordID, 
r.RoomID, 
r.RoomName,
r.RoomBooked,
r.RoomFromDate,
r.RoomToDate,
r.FullBoardRoom, 
COUNT(r.RecordID) AS RC,
r.Override,
r.HotelID,
o.Override AS ConfOverride
FROM
ConfRooms AS c
Left Join FIDS_Hardware AS h ON (c.ConfRoomID = h.deviceID AND h.hotelID = 'EXBHX')
Left Join ConfBookings AS r ON (c.ConfRoomName = r.RoomName AND r.HotelID = 'EXBHX')
Left Join ConfRoomOverride AS o ON (c.ConfRoomID = o.RoomID)
WHERE c.HotelID = 'EXBHX' 
GROUP BY c.ConfRoomID
ORDER BY h.messageCount DESC,
ConfOverride DESC, 
r.RoomBooked DESC,
c.ConfOrder ASC

问题是WHERE子句删除了多个左连接的好处。

当您将join FIDS_Hardware保留为h时，这意味着h值可以为null，但是当您在where子句中执行"h.hotelID = 'EXBHX'“时，您只选择h值不为null的行(一行已经匹配)。

因此，您需要做的是将这些条件移到left join的ON子句中，如上所述。