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

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

Answer 2

You have syntax error. You are missing END there.

Answer 3

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 4

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 5

You must understand that CASE ... END block is NOT equivalent to IF { } from C-like languages. Much rather this is equivalent to elaborate version of ... ? ... : ... operator from C-like languages. What it means that the WHOLE CASE block must essentially evaluate to single value and that this value has to be the same type no matter which case of the block is executed. This means that:

CASE
WHEN ts.ACCOUNTTYPE = '1' THEN ts.ACCOUNTID=SI.TENANCYID ...
END

Is fundamentally incorrect unless you work on a version of database that will allow you bool value as a value (SQL Server won't allow it for example but I think some of MySQL version used to allow it - not sure about this). You probably should write something like:

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

Notice how the whole CASE block evaluates to 1 or 0 and then it is compared to 1. Of course instead of 4 WHEN's you could use one WHEN with combination of AND's, OR's and ( ) brackets. Of course in this particular case answer by @ppeterka 66 is correct as CASE is not suited for what you really wanted to do - I'm just trying to clarify what CASE really is.