If you have used Microsoft T-SQL at all you have undoubtedly used JOINS to connect multiple tables based on a common key value. You have probably also used the CASE statement functionality in your SELECT to conditionally change the output based on a set of parameters. But did you know you could use both together? I recently ran into a problem where I needed to conditionally join an additional column based on the value from one of the tables.
In a simplified example below, I’m joining an Objects table to a UserAccess table.
The Object and UserAccess tables share the following columns:
Objects can either be product specific or across multiple products, users always have a product tied to them. Based on the value of the ObjectTypeID column will determine whether I want to join on the Product field. So the logic below is basically saying that if the ObjectTypeID is the value ‘Table’ I do not want to join on the Product column, if the ObjectTypeID is any other value, then I also want to join on the Product column.
FROM [Objects] obj
join [UserAccess] ua
WHEN obj.ObjectTypeID = ‘Table’ and obj.ObjectID = ua.ObjectID and obj.ObjectTypeID = ua.ObjectTypeID THEN 1
WHEN obj.ObjectTypeID <> ‘Table’ and obj.ObjectID = ua.ObjectID and obj.ObjectTypeID = ua.ObjectTypeID and obj.Product = ua.Product THEN 1
END = 1
Obviously this is a very specific use case but when needed, can simply your query significantly because otherwise you would have to do multiple SELECT queries and UNION them together.