Thursday, April 25, 2013

Conditional JOIN's

I was in a need to do a conditional join. Meaning I have two tables and wanted to join to one or the other within my 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 
  CASE @UserType 
   WHEN 'Member' THEN p.PersonId 
   ELSE NULL 
  END = m.MemberId
 LEFT JOIN erdbo.Admin a ON
  CASE @UserType
   WHEN 'Admin' THEN p.PersonId
   ELSE NULL
  END = a.AdminId
WHERE CASE @UserType
  WHEN 'Member' THEN m.MemberId
  ELSE a.AdminId
   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.




No comments:

Post a Comment