SQL Server conditional join based on column values

sql, sql-server, t-sql

Solution

The existing query looks pretty good to me, although it can be simplified slightly:

SELECT dbo.tblActionsRequired.ActionID, dbo.tblEquipment.EquipmentID
FROM dbo.tblEquipment 
JOIN dbo.tblActionsRequired 
  ON dbo.tblActionsRequired.AllSites = 'True' OR 
     (dbo.tblEquipment.AreaID IS NULL AND 
      dbo.tblEquipment.BasedAtID = dbo.tblActionsRequired.BasedAtID) OR 
     dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID

- since if both a.AreaID and e.AreaID are null, then the condition `dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID` does not evaluate as true.

(The parentheses aren't strictly required, but were included for clarity.)

Problem

I'm working on a fairly complicated aspect to an Equipment / Asset database in SQL Server and would like to ask for some help please. I'll try and focus on one issue here and then ask a separate question for another problem. I have a table for equipment called `tblEquipment` and a table for required actions that need to be performed on collections of equipment (`tblActionsRequired`), for the sake of this question the relevant fields are: - `tblEquipment`: `EquipmentID, BasedAtID, AreaID` - `tblRequiredActions`: `AllSites (bit), BasedAtID, AreaID` So the idea with `tblRequiredActions` is that you'd say all equipment at company Site A needs to be inspected every so often. Areas are specific rooms or offices etc. at a site. So if AllSites is true the action applies to all equipment company-wide, if it's false then BasedAtID (for the site) is required, AreaID is optional if you want to narrow it down even more. So now the problem to extract which actions should be applied to which equipment based on these three fields. What I have now is what I think might work but I'm struggling to verify my results as there are other factors confusing me as well, so would really appreciate some confirmation or guidance if I'm completely on the wrong track! I don't want to go the route of stored procedures and if blocks or unions as there are multiple other dimensions that need to be covered with the similar principle and I'll end up writing a massively complicated procedure that will be a nightmare to maintain. Thanks!! ``` SELECT dbo.tblActionsRequired.ActionID, dbo.tblEquipment.EquipmentID FROM dbo.tblEquipment INNER JOIN dbo.tblActionsRequired ON dbo.tblActionsRequired.AllSites = 'True' OR dbo.tblEquipment.AreaID IS NULL AND dbo.tblEquipment.BasedAtID = dbo.tblActionsRequired.BasedAtID OR dbo.tblEquipment.AreaID IS NOT NULL AND dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID ```

Original source