15

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
Improve this question
1
  • 2
    +1 well asked... Enough detail to answer your question with confidence. But please ensure clean formatting as per marc_s's edit... You'll get more interest if people can easily read what you're trying to express. Commented Mar 27, 2013 at 14:33

3 Answers 3

Reset to default
8

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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you very much Mark, makes sense, at least I now know step 1 is OK, will have to create a new question with the next complication :) Really appreciate it!
Hi @mark-bannister, just wondered if you'd mind having a look at my new question? Thank you! link
2

Looks correct to me; Your logic and approach look sound.

Although the combinations of AND/OR might be open to misinterpretation... Perhaps consider grouping your AND expressions in parenthesis (I know this is redundant, but doing so clarifies your intent). And as per @MarkBannister's recent answer, this might reveal an opportunity for Boolean algebra factorization :-)

I'd personally move any "filtering" out of the inner join's ON clause, into a WHERE clause, leaving just the expressions that relate to the join itself, in the case where there's only one join and those filters are isolated from the join logic itself. But this is not the case here.

Indexes will probably affect this one way or the other... Check your execution plan to see if you'd benefit from adding some on your FKs or the text field.

Improve this answer

3 Comments

Thank you Sepster, I will definitely check out indexes once I'm happy with the full query
...consider grouping your AND expressions in parenthesis (I know this is redundant(...)) - are you sure? I never had to try it until now (and I still didn't, will try in few mins), but parentheses are crucial in WHERE clause, so I can't imagine JOIN to be that radically different from said WHERE...
@AcePL Yes, I'm sure. AND operator has higher operator precendence than OR operator. In both WHERE and UNION expressions (and in most/all programming languages that implement classical Boolean logic).
1

I don't think you need the not null
If AreaID is null then dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID will evaluate to null

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 = dbo.tblActionsRequired.AreaID
Improve this answer

2 Comments

Ultra nitpick: If AreaID is null then dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID will evaluate to null, not false - here, that doesn't make a difference, but if you added a not, then a null expression would still evaluate as null, while a false expression would then evaluate as true.
@MarkBannister OK changed it to null

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.