Menu
A conditional column join is a fancy way to let us join to a single column and to two (or more) columns in a single query. We can accomplish this by using a case statement in the on clause of our join. A case statement allows us to test multiple conditions (like an if/else if/else) to produce a single value.
There are plenty of ways to resolve for this: a subquery with a CASE statement in the join statement for the table you are joining in, a CASE statement in a temp table where all values are changed to match, or this handy little trick of using a CASE statement in the JOIN's ON clause.
A COALESCE function returns the first non-NULL expression from a specified list. Usually, we use COALESCE as one of the elements in the select list, however, it can be successfully used in the join conditions too.
I ran some tests and the results show that it is actually very close, but the WHERE clause is actually slightly faster! =) I absolutely agree that it makes more sense to apply the filter on the WHERE clause, I was just curious as to the performance implications.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With