Postgres - Left Join, same table

Question

I have a table of subscription records for people subscribed to groups. There's a groupid column and a userid column (in addition to the primary key id). I want to find all the subscription rows of people subscribed to group 1 that do not also have a subscription record for group 2. I thought I could use an anti-join pattern, but I can't get it to work. I've tried:

SELECT
*
FROM subs s1
LEFT JOIN subs s2 ON s1.groupid=1 AND s2.groupid=2 AND s1.userid=s2.userid
WHERE s2.id IS NULL;

But that does not work (it returns subscription records with groupids that are not 1).

Gordon Linoff · Accepted Answer · 2014-10-24 04:55:36Z

4

I would use group by for this:

select s.userid
from subs s
group by s.userid
having sum(case when s.groupid = 1 then 1 else 0 end) > 0 and
       sum(case when s.groupid = 2 then 1 else 0 end) = 0;

Each condition in the having clause counts records for one of the groups. The first condition says there is at least one row with groupid = 1. The second says there are no records with groupid = 2.

You can do what you want with a left join as well. Here is the approach:

SELECT s1.*
FROM subs s1 LEFT JOIN
     subs s2
     ON s1.userid = s2.userid AND s2.groupid = 2
WHERE  s1.groupid = 1 AND s2.id IS NULL;

Because it is a left join, the condition on the first table should go in the where clause.

answered Oct 24, 2014 at 4:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Postgres - Left Join, same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related