CODEWITHSUNDEEP
phpmysql
user2210819
user2210819

Reputation: 145

show '0' instead of null when select count is null

I have two tables:

table_1 table_2

 uid | eid | cid             cid | eid | name
----------------             ----------------
  20 | 1  |  3                1  |  1  | Amy
  20 | 1  |  2                2  |  1  | Sam
  20 | 1  |  3                3  |  1  | Paul
  20 | 2  |  1                4  |  2  | June
  20 | 2  |  2                5  |  2  | Peter
  20 | 2  |  2                6  |  2  | Mary

I need the following result:

name  | number
--------------
Amy   |   0
Sam   |   1
Paul  |   2

My code is 

select t2.`name` , t2.cid, a.number from table_2 t2 
left join  table_1 t1 on t2.eid = t1.eid 
left join (select t12.cid, count(t12.cid) as number from table_1 t12
inner join table_2 t22
where t12.eid = 1 and t12.cid = t22.id group by t12.eid)a  on t2.id = a.cid 
where t1.eid = 1 group by t2.id

what I get is 

name  | number
--------------
Amy   |  null
Sam   |   1
Paul  |   2

It doesn't not work if I use 

IFNULL(count(t12.cid),0)

Any suggestions?

Upvotes: 0

Views: 109

Answers (4)

Swapnil
Swapnil

Reputation: 616

Try Using  IF(count(t12.cid)= 'null', 0, count(t12.cid))

Upvotes: 0

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

you could also do:

CASE WHEN count(t12.cid) IS NOT NULL 
       THEN count(t12.cid)
       ELSE 0
END AS some_count

OR

(count(t12.cid) IS NOT NULL) AS some_count

Upvotes: 1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798606

..., IFNULL(a.number, 0) AS number ...

Upvotes: 1

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324630

On the PHP side, you can do this:

echo $some_var_that_may_be_null ?: 0;

This will cause any falsy values (in this case, null) to become zero.

Upvotes: 4

Related Questions