查询不返回任何记录
问题描述:
您好,我正在为采购订单制定这些订单可以由某个用户订购,然后由另一个用户接收。查询不返回任何记录
所以我创建了下面的模式。
模式
的问题是当PurchaseOrders
和Deliveries
表UserID
列具有查询返回任何记录不同的值。
查询
SELECT
dbo.Users.FirstName,
dbo.Users.LastName,
dbo.PurchaseOrders.PurchaseOrderDate,
dbo.Deliveries.ExpectedDeliveryDate,
dbo.Deliveries.ActualDeliveryDate
FROM dbo.PurchaseOrders
INNER JOIN dbo.Users
ON dbo.PurchaseOrders.UserID = dbo.Users.UserID
INNER JOIN dbo.Deliveries
ON dbo.PurchaseOrders.PurchaseOrderID = dbo.Deliveries.PurchaseOrderID
AND dbo.Users.UserID = dbo.PurchaseOrders.UserID
AND dbo.Users.UserID = dbo.Deliveries.UserID
答
需要两个不同的连接到Users
。你还需要学会使用表别名:
SELECT pu.FirstName as purchase_FirstName, pu.LastName as purchase_LastName,
du.FirstName as delivery_FirstName, du.LastName as delivery_LastName,
po.PurchaseOrderDate,
d.ExpectedDeliveryDate, d.ActualDeliveryDate
FROM dbo.PurchaseOrders po JOIN
dbo.Deliveries d
ON po.PurchaseOrderID = d.PurchaseOrderID JOIN
dbo.Users pu
ON p.UserID = pu.UserID JOIN
dbo.Users du
ON d.UserId = du.UserId;
你的查询没有返回的,因为这种情况记录:
AND dbo.Users.UserID = dbo.PurchaseOrders.UserID
AND dbo.Users.UserID = dbo.Deliveries.UserID
这显然意味着,dbo.PurchaseOrders.UserID = dbo.Deliveries.UserID
。所以,如果这不是真的,那么没有记录符合条件。
答
当您使用Inner Join时,如果数据不匹配,您将不会得到任何记录。您需要使用LEFT JOIN,并且第二次连接不需要使用用户标识的条件。尝试下面的查询。
SELECT
dbo.Users.FirstName,
dbo.Users.LastName,
dbo.PurchaseOrders.PurchaseOrderDate,
dbo.Deliveries.ExpectedDeliveryDate,
dbo.Deliveries.ActualDeliveryDate
FROM dbo.PurchaseOrders
INNER JOIN dbo.Users
ON dbo.PurchaseOrders.UserID = dbo.Users.UserID
LEFT JOIN dbo.Deliveries
ON dbo.PurchaseOrders.PurchaseOrderID = dbo.Deliveries.PurchaseOrderID