CODEWITHSUNDEEP
sql-serversql-server-2008left-joincase
Tannya
Tannya

Reputation: 171

Use CASE with JOIN Statements in SQL Server

I have a requirement to select data from a few tables. The first table contains two columns Paid From and Payer Code. Depending on the Paid From, I have to return either Student Name (from Student Table) or Employee Name (from Employee Table) or Account Name (from Account Table). As of now, I used a case expression in a SELECT query. I wanted to know if it is possible to do so in a LEFT JOIN.

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

Upvotes: 2

Views: 1719

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Try something like this

SELECT RV.PayerCode,
       CASE
         WHEN RV.paidfrom = 3 THEN ''
         ELSE COALESCE(s.FullName, a.AccountName, e.EmployeeName)
       END PayerName,
FROM   dbo.Finance RV
       LEFT OUTER JOIN dbo.Students s
                    ON s.StudentID = RV.PayerCode
                       AND RV.PaidFrom = 1
       LEFT OUTER JOIN dbo.Accounts a
                    ON a.AccountNumber = RV.PayerCode
                       AND RV.PaidFrom = 2
       LEFT OUTER JOIN dbo.Employee e
                    ON e.EmployeeID = RV.PayerCode
                       AND RV.PaidFrom = 4

Note: Consider changing your database structure

Upvotes: 4

Related Questions