A co-worker asked me earlier today if there was a way to do a conditional join. Meaning he had two tables and he wanted to join to one or the other within his query, depending on the value of a variable. The two simplest ways to accomplish this are to use an IF statement based on the variable and just repeat the query with the small modification to the JOIN, or to use dynamic SQL.
I’m not a large fan of dynamic SQL and I’m definitely not a fan of duplicating queries that have such a small difference, like in the IF statement solution. I’ve run across this in the past and have come up with at least one solution that allowed me to accomplish the desired result using CASE statements. After I re-worked this example, it didn’t look familiar to me so I may have done something different in the past. Damn memory!
The idea is to use LEFT JOIN’s to either bring in the data or not, based on the variable, using CASE statements. Then, essentially the same CASE statement is used in a WHERE clause to filter out the unnecessary results.
In this example, there are three tables. Person, which is a parent table, and Member and Admin, which are child tables of Person. So a Person can either be a Member or an Admin, but not both. Here’s what the query looks like:
DECLARE @UserType varchar(25)
–SET @UserType = ‘Member’
SET @UserType = ‘Admin’
SELECT p.PersonId, m.MemberId, a.AdminId
FROM erdbo.Person p LEFT JOIN erdbo.Member m ON
WHEN ‘Member’ THEN p.PersonId
END = m.MemberId
LEFT JOIN erdbo.Admin a ON
WHEN ‘Admin’ THEN p.PersonId
END = a.AdminId
WHERE CASE @UserType
WHEN ‘Member’ THEN m.MemberId
END IS NOT NULL
When @UserType is ‘Member’, only those records where p.PersonId = m.MemberId are returned and all of the Admin records are excluded. When @UserType is ‘Admin’, only those records where p.PersonId = a.AdminId are returned and all of the Member records are excluded.
I don’t know how this would hold up in a performance test, so one of these days I’ll get around to checking that out. In the mean time, it’s a third option to the problem at hand.