WHERE and AND clause not return values

I have this PostgreSQL SQL query for selection few values from 4 tables joined by INNER JOIN, and I have problem with returning a value.

Select u.id,u.name,u.image,u.created_at as member_from, 
       p.referral, p.note, 
       CASE WHEN count(l.selfy_id)=NULL THEN '0' ELSE count(l.selfy_id) END as likes_total,
       CASE WHEN count(s.id)=NULL THEN '0' ELSE count(s.id) END as selfies_total 
from users as u
inner join profiles  p on p.user_id = u.id
inner join selfies  s on s.user_id = u.id
inner join likes  l on l.selfy_id = s.id
where (u.active = true and s.active = true and u.id= 2 )
group by u.id,u.name,u.image,member_from,p.referral,p.note;

If I exclude in where block s.active = true I am getting some result but when included its not returns anything.

In tables selfies I have 4 rows with active true and one with active false values.

Solution was left join on likes table.

1 answer

  • answered 2018-04-14 15:41 Gordon Linoff

    This is too long for a comment and not relevant to the question being asked (which apparently was solved using left join).

    COUNT() never returns NULL values. And a CASE expression only returns a single value with a single type. And, basically all comparisons to NULL return NULL, which is treated as FALSE. Given these facts, can you name three things wrong with this expression?

       CASE WHEN count(l.selfy_id)=NULL THEN '0' ELSE count(l.selfy_id) END as likes_total,
    

    You can formulate the SELECT as:

    Select u.id, u.name, u.image, u.created_at as member_from, 
           p.referral, p.note, 
           count(l.selfy_id) as likes_total, count(s.id) as selfies_total 
    

    Now, these are going to return exactly the same values, because COUNT() counts non-NULL values and both of the argument to COUNT() are used in JOIN conditions -- so are never NULL.

    I speculate that you really intend:

    Select u.id, u.name, u.image, u.created_at as member_from, 
           p.referral, p.note, 
           count(distinct l.selfy_id) as likes_total,
           count(distinct s.id) as selfies_total 
    

    although I am not 100% sure those ids are the right ones to be counting.