CODEWITHSUNDEEP
sql
Gambit2007
Gambit2007

Reputation: 3986

SQL - using "CASE" after an "AND"

I have the following SQL query:

SELECT 
    ts.id, t.id, t.name
FROM 
    table1 ts
INNER JOIN 
    tabble2 t ON ts.ta = t.ta
              AND CASE 
                     WHEN :var1 = 0 THEN ts.id = :var1
                     ELSE ts.id = :var1 AND ts.em = :var2
                  END
ORDER BY  
    ts.id DESC;

var1 and var2 are variables I'm passing from code.

Is there something wrong with that query? For some reason it doesn't seem to provide the correct results.

Upvotes: 0

Views: 37

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82504

I would probably write it like this:

SELECT ts.id, t.id, t.name
       FROM table1 ts
            INNER JOIN tabble2 t 
                   ON ts.ta = t.ta
                    AND ts.id = :var1
                    AND (:var1 = 0 OR ts.em = :var2)
                     ORDER BY ts.id desc;

Upvotes: 1

jarlh
jarlh

Reputation: 44796

SELECT ts.id, t.id, t.name
FROM table1 ts
INNER JOIN tabble2 t 
   ON ts.ta = t.ta
   AND ((:var1 = 0 AND ts.id = :var1) OR (:var1 <> 0 AND ts.id = :var1 AND ts.em = :var2))
ORDER BY ts.id desc;

Upvotes: 3

Related Questions