Select only a row if one of the joined columns meets both conditions in Postgresql

Question

Please suppose these two tables:

users table:

id	account_id
1	1
2	1
3	1
4	1

user_labels table:

id	user_id	title
1	2	label1
2	2	label2
3	1	label1
4	1	label2

now I want to select users who have both label1 and label2 columns, the users which has only one of these should not be returned in the result. for example, users 3 and 4 have only one of the labels then they should not appear in the result.

Please take a look at the following query, It will not work correctly because logically title column cannot be both 'label1' and 'label2'.

select u.id, STRING_AGG(l.title, ', ') AS titles from users u 
left outer join user_labels l on l.user_id = u.id 
where l.title = 'label1' AND l.title = 'label2';

how can I achieve this in PostgreSQL?

Answer 1

I don't see a group by with string_agg. Add the group by, then add a having clause:

select u.id, string_agg(l.title, ', ') as titles
from users u 
join user_labels l on u.id = l.user_id
where l.title in ('label1', 'label2')
group by u.id
having count(distinct l.title) = 2

Answer 2

You can use below query,

select id, titles from
(select u.id, STRING_AGG(l.title, ', ') AS titles,
count() over(partition by l.title) as cnt
 from users u 
left outer join user_labels l on l.user_id = u.id) T1
where cnt=2;