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

Postgresql – Searching for records that meet specific conditions

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:

2

Answers


  1. 0

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

Back To Top
Search