case not working as desired in mysql query

Question

im using the foloowing query with case statement--

SELECT case WHEN t1.abc !='' THEN 1 ELSE 0 END as ids FROM
 table1 AS t1 LEFT JOIN table2 as t2
 ON t1.pid = t2.id
 WHERE t2.idr IS NULL and t1.idr=23

in it im getting 1 in ids when query returns a row but when query returns nothing then 0 is not returned.why is that so??

What i need is that when query returns a row it should return 1 and if no result is shown then it should return 0. where am i going wrong??

Check this http://stackoverflow.com/questions/334108/how-do-i-check-if-a-sql-server-string-is-null-or-empty — ariel, May 09 '11 at 05:54
Ahh! Now that i see the question, sorry. That's not how it works. You should check if the value is returned or not after the query. — ariel, May 09 '11 at 05:57
for id 23 my query retuns nothing i.e. no value and in that case i want it to return 0. — developer, May 09 '11 at 06:02
wt u mean by getting 1 when query returns a row... bcoz case works for column values only. so if t1.abc column is existing & it has empty value then only it will return 0 per row. can u illustrate by giving eg: for better solution — Angelin Nadar, May 09 '11 at 06:04

score 0 · Answer 1 · edited May 23 '17 at 11:55

If you only care how to get a 1 or 0. Try the example query below

SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END ids
    FROM table1 t1 INNER JOIN table2 t2 ON t1.pid = t2.id
    WHERE t2.idr IS NULL
    AND t1.idr = 123;

In your case the reason why your not getting 0 when there's now row. It is because there nothing to compare. Well this is only a guess, but this is what I think.

As an alternative let php handle if the results return no rows. See it here

case not working as desired in mysql query

1 Answers1