您好,我正在制定采购订单的架构,这些订单可以由某些用户订购,然后由其他用户接收。
问题是,当“传递”表中的UserID值为null时,查询不返回任何记录。
UserID值为null,因为尚未交付订单。
询问
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 po.UserID = pu.UserID JOIN
dbo.Users du
ON d.UserId = du.UserId;
架构图
您最有可能需要在此处加入LEFT JOIN。稍加格式化即可使很多内容更易于阅读。您可以看一下本文以了解不同类型的联接。http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
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.Users pu ON po.UserID = pu.UserID
left JOIN dbo.Deliveries d ON po.PurchaseOrderID = d.PurchaseOrderID
left JOIN dbo.Users du ON d.UserId = du.UserId;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句