How would I do this conditional join in MS SQL?

Question

I searched around for conditional joins, but it seems like they're trying to do something different that what I'm trying. I'll simplify the problem a bit for the question.

I have three tables: Users, Clients, and Employees.

The Users table has two columns, AssociatedID and UserType.

UserType is either "Client" or "Employee," and AssociatedID is a corresponding ID in either the Clients or the Employees table.

The problem is, Clients and Employees both use integers for IDs, so each table could have the same ID for something completely different (as opposed to GUIDs, which would avoid this problem).

What I want is a query that will look at a row in Users and say if UserType is Employee, then JOIN AssociatedID on the Employees table's ID, and if UserType is Client, then JOIN AssocaitedID on the Clients table's ID. So if Clients and Employees both have a Name column, I could get all of the names that go with each User, regardless of if they're a Client or an Employee. I'm using SQL Server 2008 R2 if that makes a difference here.

Answer 1

Try this:

SELECT 
  COALESCE(c.Name, e.Name) AssociatedName,
  ... 
FROM Users u
LEFT JOIN Clients c    ON u.AssociatedID = c.ClientId
                      AND u.UserType     = 'Client'
LEFT JOIN Employees e  ON u.AssociatedID = e.EmployeeId
                      AND u.UserType     = 'Employee';

SQL Fiddle demo

Or: As @Tikkes pointed out, you can do this with UNION like this:

SELECT c.Name AssociatedName
FROM Users u
INNER JOIN Clients c    ON u.AssociatedID = c.ClientId
                      AND u.UserType     = 'Client'
UNION ALL
SELECT e.Name FROM Users u
INNER JOIN Employees e  ON u.AssociatedID = e.EmployeeId
                      AND u.UserType     = 'Employee';

Updated SQL Fiddle Demo

---

Update: For this you have to use the CASE expression like this:

SELECT 
  u.AssociatedID,
  CASE u.UserType 
   WHEN 'Client' THEN c.Name
   ELSE e.Name 
  END AS AssociationName
FROM Users u
LEFT JOIN Clients c    ON u.AssociatedID = c.ClientId      
LEFT JOIN Employees e  ON u.AssociatedID = e.EmployeeId;

Updated SQL Fiddle Demo using CASE