postgres aggregate not showing rows where LEFT JOIN table has nulls

Question

I have a table that list email addressed for a member and has a boolean is_selected. The idea is that only one should be selected. I am joining the table to itself with a where clause on is_selected. I want to

string_agg(u.email, ',')

to show all the unselected emails in one column/row in a view.

Here is a fiddle.

My problem is that I can't get the view to work in cases where there are no entries that are unselected.

sql fiddle is having issues today so:

CREATE TABLE member_email
  (
    member integer NOT NULL, -- reference to another table
    email character varying(150) NOT NULL,
    is_selected boolean NOT NULL,
    PRIMARY KEY(member,email)
  );

INSERT INTO member_email 
    (member,email,is_selected) 
    VALUES 
        (2,'[email protected]',TRUE),
        (2,'[email protected]',FALSE),
        (2,'[email protected]',FALSE),
        (3,'[email protected]',TRUE),
        (3,'[email protected]',FALSE),
        (4,'[email protected]',TRUE);

CREATE VIEW v_member_email AS 
    SELECT s.member
    ,s.email as selected_email
    ,string_agg(u.email, ',') as unselected_email 
    FROM member_email s 
    LEFT JOIN member_email u 
        ON s.member = u.member 
    WHERE s.is_selected = TRUE 
        AND u.is_selected = FALSE 
    GROUP BY s.member,s.email 
    ORDER BY member;

SELECT * FROM v_member_email;

-- where is [email protected] in result?

... AND u.is_selected = FALSE ... . Since you are testing an attribute from the left-joined u table, the leftjoin effectively degrades into a plain join. You could use ` ... AND u.is_selected IS DISTINCT FROM True ...` instead. — joop
– joop, Commented Aug 13, 2013 at 16:06

Clodoaldo Neto · Accepted Answer · 2013-08-13 16:05:54Z

1

SQL Fiddle

If the condition on the right side is put in the where clause you transform the left join into an inner join. Just move it to the join condition:

select
    s.member
    ,s.email as selected_email
    ,string_agg(u.email, ',') as unselected_email 
from
    member_email s 
    left join
    member_email u on
        s.member = u.member
        and not u.is_selected
where s.is_selected
group by s.member,s.email 
order by member;

answered Aug 13, 2013 at 16:05

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

postgres aggregate not showing rows where LEFT JOIN table has nulls

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