Postgresql find by two columns-CodePudding

I have join table tags_videos

 tag_id | video_id
-------- ----------
   1195 |    15033
   1198 |    15033
   1199 |    15033

   1196 |    15034
   1198 |    15034
   1199 |    15034

   1197 |    15035
   1198 |    15035
   1199 |    15035

   1195 |    15036
   1197 |    15036
   1198 |    15036

How can I select distinct video_id who have two specific tag_id

For example my tag_ids is 1195 and 1198, i should get video_ids 15033 and 15036 (who have 1195 and 1198 tag_id)

CodePudding user response：

Extract the unique (tag_id, video_id) pairs for the two tags in t CTE and select these video_id's that have both tag_id's (i.e. 2 occurrences).

with t as 
(
 select distinct tag_id, video_id
 from tags_videos 
 where tag_id in ('1195', '1198')
)
select video_id from t
group by video_id having count(*) = 2;

DB-fiddle demo