how to check row contains all given values in postgres

Question

Have 2 table table a and table b,

table A - id (primary key) table B - aId(foreign key), userId, date

ex :

table A

|id|
|--|
|1|
|2|
|3|

table B

|aId (foreign key)|userId|
|--|--|
|1|1|
|1|2|
|2|1|
|3|3|

need to fetch id which contains both 1 and 2 userId

tried by below SQL with in but returns either but not both

SELECT ta.id 
FROM tableA ta 
JOIN tableB tb ON ta.id = tb.taId 
WHERE tb.userId in (1,2);

I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. — user330315
– user330315, Commented May 4, 2021 at 7:30

Praveen · Accepted Answer · 2021-05-04 04:34:06Z

1

one way using intersect.

select taid from tableb where userid = 1
intersect
select taid from tableb where userid = 2

another using group by

select
taid
from
tableb
where
userid in (1, 2)
group by
taid
having
count(distinct userid) > 1

answered May 4, 2021 at 4:34

Praveen

4155 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

George Joseph · Accepted Answer · 2021-05-04 05:43:46Z

0

with data
  as (select 1 as userid
      union all
      select 2 as userid
      )
   select m.tableaid
         ,count(distinct n.userid)
     from data m
left join tableB n
       on m.userid=n.userid
 group by m.tableaid
having count(distinct n.userid)=2

answered May 4, 2021 at 5:43

George Joseph

5,93213 silver badges25 bronze badges

Collectives™ on Stack Overflow

how to check row contains all given values in postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related