PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search