Postgresql - Searching for records that meet specific conditions

dafie
November 19, 2024
188 views
0 votes
2 Answers

This is my database design:

CREATE TABLE post (
  id INT,
  user_id INT,
  type_id INT
);

I want to get a list of users who wrote posts of types 1 and 2 only. This seems to be working:

select distinct user_id
from post p1
where exists
(
  select 1
  from post p2
  where p1.user_id = p2.user_id
  and type_id = 1
)
and exists
(
  select 1
  from post p2
  where p1.user_id = p2.user_id
  and type_id = 2
)
and not exists
(
  select 1
  from post p2
  where p1.user_id = p2.user_id
  and type_id not in (1,2)
)

I have a feeling this can be done easier. Any ideas?

Tags: postgresql sql

Answers

There are quite a few ways to do it with GROUP BY and HAVING.

Here are a few…

SELECT
  user_id
FROM
  post
GROUP BY
  user_id
HAVING
      MIN(type_id) = 1
  AND MAX(type_id) = 2

SELECT
  user_id
FROM
  post
GROUP BY
  user_id
HAVING
      COUNT(DISTINCT type_id) = 2
  AND MAX(CASE WHEN type_id IN (1,2) THEN 0 ELSE 1 END) = 0

SELECT
  user_id
FROM
  (SELECT DISTINCT user_id, type_id FROM post) p
GROUP BY
  user_id
HAVING
   SUM(CASE WHEN type_id IN (1,2) THEN 1 ELSE 3 END) = 2

Or using set operators…

SELECT user_id FROM post WHERE type_id = 1
INTERSECT
SELECT user_id FROM post WHERE type_id = 2
EXCEPT
SELECT user_id FROM post WHERE type_id NOT IN (1, 2)

- samhita
- November 19, 2024 at 1:56 pm
- 0 votes
0
Since you mentioned your query returns correct answer, I compared output of my query against yours and it matches, let me know if you observe any differences, I can edit the answer.

To test the case, I have entered 3 sets of data,
- user_id 1 has type 1 and 2
- user id 2 has type 1, 2 and 4(so should be excluded from output)
- user id 3 has type 1 (should be excluded)
Here is the fiddle
```
    SELECT user_id
FROM post
GROUP BY user_id
HAVING COUNT(DISTINCT type_id) = 2
  AND SUM(CASE WHEN type_id IN (1, 2) THEN 1 ELSE 0 END) = 2;
```
Login or Signup to reply.