sql - Searching for records that meet specific conditions - Stack Overflow

admin管理员组

文章数量:1431459

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?

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?

• sql
• postgresql

Share Improve this question Follow asked Nov 19, 2024 at 12:09 dafiedafie 1,1691010 silver badges2828 bronze badges 4

• You can do a GROUP BY. Or use EXCEPT. – jarlh Commented Nov 19, 2024 at 12:24
• Does 'types 1 and 2 only' mean both 1 and 2? – jarlh Commented Nov 19, 2024 at 12:25
• should the output contain the user_id if they had topic_id other than 1 and 2, for example if user_id 1 has post_id 1,2,3 then should the user_id 1 be included? or it should be exclusively topic 1 and 2 – samhita Commented Nov 19, 2024 at 12:28
• Do you have a user table? You should do, and using that with EXISTS will probably be quickest. – MatBailie Commented Nov 19, 2024 at 12:48

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

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 0 ELSE 1 END) = 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)

another solution using exists operator:

select user_id 
from post p1 
where type_id in(1,2)
  and not exists (
                 select * from post p2
                 where p1.user_id=p2.user_id
                 and p2.type_id  not in (1,2))
group by user_id
having count(distinct type_id)=2;

Hope it helps.

本文标签： sqlSearching for records that meet specific conditionsStack Overflow