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

Postgresql – How to select from a table all records for which there are other records in this table with certain parameters?

I have a table like this in Postgres:

user_id project status
1 p1 finished
2 p1 finished
2 p2 finished
2 p3 finished
3 p1 finished
3 p2 finished
3 p3 finished
4 p1 finished
5 p1 finished
6 p1 finished
6 p2 finished

I need to select all users who have finished projects p1, p2, p3. (in this sample table it will be user_id 2 and 3.
If I use where project in ('p1', 'p2', 'p3') , it will return any of these projects, not all of them. Unfortunately, projects are out of order, otherwise I would use row_number() and select all rows where row_number = 3. So, in my query I need to mention clearly, users with which projects finished I want to see in my result table

As a result I need this:

user_id
2
3
Tags:

2

Answers


  1. 0

    Having

    create table projects (user_id int, project varchar, status varchar);

    Then

    select user_id, array_agg(project) projs   
from projects 
where status ='finished' 
group by user_id
having array_agg(project)::text[] @> array['p1','p2','p3'] 
order by user_id;

    Working sample

    Login or Signup to reply.
  2. 0

    In order to achieve this result, you can use a query with the HAVING and GROUP BY clause to filter the results:

    SELECT user_id
FROM project_table
WHERE project IN ('p1', 'p2', 'p3')
GROUP BY user_id
HAVING COUNT(DISTINCT project) = 3;

    The query will select the user_ids which have finished projects p1, p2 and p3.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search