CODEWITHSUNDEEP
sqlpostgresqlwhere-clause
Nezir
Nezir

Reputation: 6925

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.

Upvotes: 1

Views: 413

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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.

Upvotes: 1

Related Questions