Postgres left join is truncating rows

Question

I would just like a join to return 0 rather than dropping rows. Currently, if I try to join the two tables and count the number of items for each subscription, it will work fine:

SELECT S.id, count(I.id) FROM "Subscription" S
LEFT JOIN "Item" I on I.id = S.item_id
WHERE S.status = 1
GROUP BY S.id

returns:

id | count(I.id) 
1       4
2       3
3       2

whereas:

SELECT S.id, count(I.id) FROM "Subscription" S
LEFT JOIN "Item" I on I.id = S.item_id
WHERE S.status = 1
AND I.status IN (1,2,3)
GROUP BY S.id

returns:

id | count(I.id) 
1       3

I realise this is probably how Postgres should work, but Im used to using MySQL.

I've tried aggregate functions to produce the desired effect as well as conditional statements and right, full and outer joins but cannot seem to get it working the way I want.

What if I want to return all of the users with status 1 but show 0 items if they have no items with status 1,2 or 3?

From this documentation it seems like the code Im using should work.

The desired result is:

id | count(I.id) 
1       3
2       0
3       0

Please try to explain better the expected result that you want — carexcer
– carexcer, Commented Jan 21, 2014 at 15:22

Ihor Romanchenko · Accepted Answer · 2014-01-21 15:25:41Z

2

Try something like:

SELECT S.id, count(I.id) FROM "Subscription" S
LEFT JOIN "Item" I on I.id = S.item_id AND I.status IN (1,2,3)
WHERE S.status = 1
GROUP BY S.id

Your old where clause was filtering the results after the LEFT JOIN turning it into a INNER JOIN.

answered Jan 21, 2014 at 15:25

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RonnyKnoxville Over a year ago

Perfect! You absolute beauty thats been having me round the houses for ages. I wasnt aware you could use "on" and "and" in a join statement. Can you do this in MySQL?

Ihor Romanchenko Over a year ago

@JackalopeZero Haven't tried it in MySQL but I am 99% sure you can do it in any RDBMS.

Craig Ringer Over a year ago

@JackalopeZero Of course - a left join predicate is an expression like anything else.

Collectives™ on Stack Overflow

Postgres left join is truncating rows

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related