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

2021-09-10 00:00:00 sql tsql sql-server

我在 MSSQL 中为客户开发的资产数据库出现问题.它需要捕获Required Actions，例如Lifting Equipment 在特定位置需要在购买后 6 个月进行检查.这些所需操作的到期日期可以用不同的方式计算，但为了简化这里将根据它们的购买日期计算.

为此，我有一个名为 tblActionsRequired 的表，其中包含以下相关字段:

  • ActionID - 用于所需的操作
  • EquipmentCategoryID 或 EquipmentTypeID 或 EquipmentID - 所以这些字段之一是必需的.有了这个，他们指定需要对一类设备或设备类型或特定设备采取行动.例如，一个 2 公斤粉状消防栓是一种设备类型，它属于消防安全设备类别，并且可能有一个特定的 2 公斤粉状消防栓，其资产编号为 PFH2KG001.
  • BasedAtID - 公司的分支机构或站点

理想情况下，我想做的是在一个查询中保留尽可能多的内容，而不是为每个组合创建单独的查询或视图，然后使用 UNION 将它们添加在一起.我还有其他几个类似的字段，可以通过这些字段对这些必需的操作进行分段，因此在这里使用联合似乎很简单，但我计算出我需要满足 48 种不同的组合，并可能为每个组合创建一个视图，然后将它们联合起来一起！

所以接下来我有包含以下相关键的 tblEquipment:

  • EquipmentID - 主键
  • EquipmentTypeID = 外键，该资产所属的设备类型
  • BasedAtID - 外键，资产所在的站点

Equipment Types 然后属于 Equipment Categories，然后类别允许构建具有父子关系的树结构，但这些我认为我已经足够照顾了在创建具有以下字段的名为 vwCategoryTree 的视图时:

  • ParentCategoryID
  • EquipmentTypeID

这个视图已经过测试并且检查得很好，它贯穿了树结构，并允许您在 EquipmentTypeID 和它们的最终父级之间使用 EquipmentCategoryID 执行连接.

我需要帮助的是如何根据 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 required
  • EquipmentCategoryID 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 sites

Ideally 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 key
  • EquipmentTypeID = foreign key, which Equipment Type this asset is a member of
  • BasedAtID - foreign key, which site the asset is located at

The 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:

  • ParentCategoryID
  • EquipmentTypeID

This 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 e
left join vwCategoryTree c on e.EquipmentTypeID = c.EquipmentTypeID
join tblActionsRequired r 
  on (e.EquipmentID = r.EquipmentID or 
      e.EquipmentTypeID = r.EquipmentTypeID or 
      c.ParentCategoryID = r.EquipmentCategoryID)

相关文章