我正在尝试编写一个查询,该查询创建一个列表,显示某一天库存中还剩下多少每件商品。
我有以下表格:
tbl类型
- Types
客房
- Room
- Type
日期
- Date
- Type
我有以下疑问:
--qryRoomInventory--
SELECT
tblTypes.Type, Count(tblRooms.Type) AS Inventory
FROM
tblTypes
INNER JOIN
tblRooms ON tblTypes.Type = tblRooms.Type
GROUP BY
tblTypes.Type;
--qryBooked--
SELECT
tblDates.Type, tblDates.Date, Count(tblDates.Type) AS Booked,
[Inventory]-[Booked] AS Opened
FROM
(tblTypes
INNER JOIN
tblDates ON tblTypes.Type = tblDates.Type)
INNER JOIN
qryRoomInventory ON tblTypes.Type = qryRoomInventory.Type
GROUP BY
tblDates.Type, tblDates.Date, qryRoomInventory.Inventory
HAVING
(((tblDates.Date) = Date()));
问题是它只显示tblDates
中的类型,但删除了其余的类型。如何让它显示整个类型列表,如果它不在列表中,则假设有 0 个预订?
为此使用LEFT JOIN
。 例如,您的第一个查询将是:
SELECT tblTypes.Type, Count(tblRooms.Type) AS Inventory
FROM tblTypes LEFT JOIN
tblRooms
ON tblTypes.Type = tblRooms.Type
GROUP BY tblTypes.Type;
如果您有多个INNER JOIN
(例如第二个查询),则应将它们全部替换为 LEFT JOIN
。