时间戳

首页 > 解决方案 > 从多个 (4) 表中返回记录

mysql - 从多个 (4) 表中返回记录

问题描述

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

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

从“ConfRooms AS c”返回所有记录，其中 c.HotelID = 'EXBHX'。

从“FIDS_Hardware AS h”返回所有记录，其中 r.HotelID = 'EXBHX' AND c.ConfRoomID = h.deviceID。

从“ConfBookings AS b”返回所有记录，其中 HotelID = 'EXBHX' AND c.ConfRoomName = b.RoomName。

从“ConfRoomOverride AS o”返回所有记录，其中 HotelID = 'EXBHX' AND 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

标签： mysql

解决方案

试试这个：

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 子句消除了多个左连接的好处。

当您离开加入 FIDS_Hardware AS h 时，这意味着 h 值可以为空，但是当您在 where 子句中执行“h.hotelID = 'EXBHX'”时，您只选择 h 值不为空的行（行已匹配） .

因此，您需要做的就是将这些条件移到上面左连接的 ON 子句中。

推荐阅读