使用第三个表的条件 SQL 连接

我需要帮助的是如何根据 EquipmentCategoryID、EquipmentTypeID 或 EquipmentID 字段中的哪些字段在 tblActionsRequired 和 tblEquipment 之间进行某种条件连接.如果只能指定 EquipmentID 或 EquipmentTypeID 那么我认为这会起作用:ON (tblActionsRequired.EquipmentID 不是 NULL AND tblEquipment.EquipmentID = tblActionsRequired.EquipmentID) OR (tblActionsRequired.EquipmentTypeID IS NOT NULL AND tblActionsRequired.EquipmentTypeID = tblEquipment.EquipmentTypeID)但是我如何将第三个表带入这个连接以满足 EquipmentCategoryID 或至少避免必须使用 UNION?对不起，如果有什么不合理的，请尽管问！非常感谢！ 解决方案 一种可能的方法:选择...来自 tblEquipment e在 e.EquipmentTypeID = c.EquipmentTypeID 上左加入 vwCategoryTree c加入 tblActionsRequired r在 (e.EquipmentID = r.EquipmentID 或e.EquipmentTypeID = r.EquipmentTypeID 或c.ParentCategoryID = r.EquipmentCategoryID)I've got a problem with an Asset Database that I have been developing for a customer in MSSQL. It entails capturing Required Actions, for example Lifting Equipment at a specific location needs to be inspected 6 months after purchase. The Due Dates for these required actions can be calculated in different ways but to simplify here will be calculated based on their Purchase Date.So to that end I have a table called tblActionsRequired that contains the following relevant fields:ActionID - for the action requiredEquipmentCategoryID or EquipmentTypeID or EquipmentID - so either one of these fields are required. With this they specify that an action is required for either a category of equipment or an equipment type or a specific piece of equipment. So an example would be that a 2kg Powder Fire Hydrant would be an equipment type, it would fall into the category Fire Safety Equipment and there might be a specific 2kg Powder Fire Hydrant with an asset number of say PFH2KG001.BasedAtID - the company's branches or sitesIdeally what I'd like to do is keep as much as possible in one query as opposed to creating separate queries or views for every combination and then adding them together using UNIONs. I have several other similar fields by which these required actions can be segmented so it may seem simple enough here to just use unions but I've calculated I would need to cater for 48 different combinations and probably create a View for each and then UNION them together!So next I have tblEquipment that contains the following relevant keys:EquipmentID - the primary keyEquipmentTypeID = foreign key, which Equipment Type this asset is a member ofBasedAtID - foreign key, which site the asset is located atThe Equipment Types then belong to Equipment Categories and the Categories then allow building a tree structure with parent-child relationships, but these I think I have sufficiently taken care of in creating a view called vwCategoryTree with the following fields:ParentCategoryIDEquipmentTypeIDThis view has been tested and checks out fine, it cuts through the tree structure and allows you to perform joins between EquipmentTypeID and their ultimate parents with EquipmentCategoryID.What I need help with is how to do some sort of conditional join between tblActionsRequired and tblEquipment based on which of the fields EquipmentCategoryID, EquipmentTypeID, or EquipmentID have a value. If only EquipmentID or EquipmentTypeID could be specified then I think this would work: ON (tblActionsRequired.EquipmentID IS NOT NULL AND tblEquipment.EquipmentID = tblActionsRequired.EquipmentID) OR (tblActionsRequired.EquipmentTypeID IS NOT NULL AND tblActionsRequired.EquipmentTypeID = tblEquipment.EquipmentTypeID)But how do I bring a third table into this join to cater for EquipmentCategoryID or at least avoid having to use a UNION?Sorry if something doesn't make sense, please just ask! Thank you very much! 解决方案 One possible approach:select ...from tblEquipment eleft join vwCategoryTree c on e.EquipmentTypeID = c.EquipmentTypeIDjoin tblActionsRequired r on (e.EquipmentID = r.EquipmentID or e.EquipmentTypeID = r.EquipmentTypeID or c.ParentCategoryID = r.EquipmentCategoryID) 这篇关于使用第三个表的条件 SQL 连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 阿里云证书，YYDS！