title: Use CASE with JOIN Statements in SQL Server
body: <p>I have a requirement to select data from a few tables. The first table contains two columns <code>Paid From</code> and <code>Payer Code</code>. Depending on the <code>Paid From</code>, I have to return either <code>Student Name</code> (from Student Table) or <code>Employee Name</code> (from Employee Table) or <code>Account Name</code> (from Account Table). As of now, I used a case expression in a <code>SELECT</code> query. I wanted to know if it is possible to do so in a <code>LEFT JOIN</code>.</p>
  
  <pre><code>SELECT  RV.PayerCode ,
          ( SELECT    CASE WHEN PaidFrom = 1
                           THEN ( SELECT  FullName
                                  FROM    Students
                                  WHERE   StudentID = RV.PayerCode
                                )
                           WHEN paidFrom = 2
                           THEN ( SELECT  AccountName
                                  FROM    Accounts
                                  WHERE   AccountNumber = RV.PayerCode
                                )
                           WHEN paidfrom = 3 THEN ''
                           WHEN paidFrom = 4
                           THEN ( SELECT  EmployeeName
                                  FROM    Employee
                                  WHERE   EmployeeID = RV.PayerCode
                                )
                      END
          ) AS PayerName 
  
  FROM    dbo.Finance RV
  </code></pre>