Use Case Statement in Join

Question

Hi every one i want to use case statement in join using this query and got error

Select CONVERT(VARCHAR(10), SII.SIDATE,103)DATE,SII.SALEID,SII.ItemName,SI.TenancyID

FROM F_SALESINVOICEITEM SII
INNER JOIN F_SALESINVOICE SI ON  SI.SALEID=SII.SALEID 
INNER JOIN #TempTableSearch ts ON CASE
 WHEN ts.ACCOUNTTYPE = '1' THEN ts.ACCOUNTID=SI.TENANCYID
  WHEN ts.ACCOUNTTYPE='2' THEN ts.ACCOUNTID=SI.EMPLOYEEID
   WHEN ts.ACCOUNTTYPE='3' THEN ts.ACCOUNTID=SI.SUPPLIERID
    WHEN ts.ACCOUNTTYPE='4' THEN ts.ACCOUNTID=SI.SALESCUSTOMERID

Error

Incorrect syntax near '='.

Please help me to solve this error.

Answer 1

IT should be,

ON 
ts.ACCOUNTID =  CASE
                    WHEN ts.ACCOUNTTYPE = '1' THEN SI.TENANCYID
                    WHEN ts.ACCOUNTTYPE = '2' THEN SI.EMPLOYEEID
                    WHEN ts.ACCOUNTTYPE = '3' THEN SI.SUPPLIERID
                    WHEN ts.ACCOUNTTYPE = '4' THEN SI.SALESCUSTOMERID
                END

Answer 2

Instead of using CASE, I'd much rather do this:

Select CONVERT(VARCHAR(10), SII.SIDATE,103)DATE,SII.SALEID,SII.ItemName,SI.TenancyID
FROM F_SALESINVOICEITEM SII
INNER JOIN F_SALESINVOICE SI ON  SI.SALEID=SII.SALEID 
INNER JOIN #TempTableSearch ts ON
       (ts.ACCOUNTTYPE='1' AND ts.ACCOUNTID=SI.TENANCYID)
    OR (ts.ACCOUNTTYPE='2' AND ts.ACCOUNTID=SI.EMPLOYEEID)
    OR (ts.ACCOUNTTYPE='3' AND ts.ACCOUNTID=SI.SUPPLIERID)
    OR (ts.ACCOUNTTYPE='4' AND ts.ACCOUNTID=SI.SALESCUSTOMERID)

To explain why the query didn't work for you: the syntax of the CASE requires an END at the end of the clause. It would work, as the other solutions proposed suggest, but I find this version to be more convenient to understand - although this part is highly subjective.

Answer 3

you can do this, so you have no chance to misspell something (note that ACCOUNTTYPE and ACCOUNTID used only when needed, you don't have to copy-paste it)

select
    convert(varchar(10), SII.SIDATE,103) as DATE,
    SII.SALEID, SII.ItemName, SI.TenancyID
from F_SALESINVOICEITEM as SII
    inner join F_SALESINVOICE as SI on SI.SALEID = SII.SALEID 
    outer apply (
        '1', SI.TENANCYID
        '2', SI.EMPLOYEEID
        '3', SI.SUPPLIERID
        '4', SI.SALESCUSTOMERID
    ) as C(ACCOUNTTYPE, ACCOUNTID)
    inner join #TempTableSearch as ts on
        ts.ACCOUNTTYPE = C.ACCOUNTTYPE and ts.ACCOUNTID = C.ACCOUNTID