CODEWITHSUNDEEP
sqlpostgresqlpostgresql-10
Reinderien
Reinderien

Reputation: 15231

Counting with a left join

I've written this SQL (which works):

with mark_derek as (
  select rachel_id
  from chad_patrick cp
  where cp.name in ('p_mark', 'p_derek')
)
select r.id as rachel_id,
       count(md.rachel_id) as total
from rachel r
     left join mark_derek md on md.rachel_id = r.id
group by r.id;

The left join is important because the returned total column should be 0 for anything that didn't join.

I tried to condense it so that it doesn't use a CTE, by changing the cp.name in to cp.name is null or cp.name in to attempt to accommodate the left join, but it didn't work. It seemed as if the is null was being ignored and the row was being omitted if the left join failed.

Is there a way to fix this so that it doesn't need a subquery or CTE?

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can eliminate the CTE like this:

select r.id as rachel_id, count(cp.rachel_id) as total
from rachel r left join
     chad_patrick cp
     on cp.rachel_id = r.id and
        cp.name in ('p_mark', 'p_derek')
group by r.id;

The filtering conditions needs to go in the on clause.

Upvotes: 1

Related Questions