Select rows which has multiple values in junction table

Question

I have three tables:

posts (id, content)
posts_tags (posts_id, tags_id)
tags (id, tag)

How do I select all posts that have (at least) 2 specific tags (lets say tags with id 1 and 2)?

For example, posts table:

  id   content   
 ---- ------- 
  1    post1  
  2    post2  
  3    post3

tags table:

  id   tag   
 ---- ------ 
  1    tag1  
  2    tag2  
  3    tag3

posts_tags table:

  posts_id   tags_id  
 ---------- --------- 
  1          1        
  1          2        
  2          1        
  3          1        
  3          2        
  3          3

I then expect the following result:

  id   content  
 ---- --------- 
  1    post1    
  3    post3

Post with ID 3 (since it has tags 1, 2, and 3) and post with id 1 (since it has tags with id 1, and 2) but not post 2 since it doesn't have tag with id 2.

Assume I can not change the table structure.

Please **[EDIT]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question - do **not** post code or additional information in comments. — , Feb 22 '18 at 17:34

Jim Jones · Answer 1 · 2018-02-22T19:53:11.510

1

SELECT * 
FROM posts p
JOIN posts_tags pt ON pt.posts_id = p.id
WHERE pt.tags_id IN (1,2);


SELECT * 
FROM posts p
JOIN posts_tags pt ON pt.posts_id = p.id
WHERE pt.tags_id = 1 OR pt.tags_id = 2;


SELECT * 
FROM posts p
JOIN posts_tags pt ON pt.posts_id = p.id
WHERE pt.tags_id = 1 AND pt.tags_id = 2;

EDIT: Quick and dirty

WITH j AS (
SELECT  pt.posts_id AS post, 
    p.content AS content, 
    STRING_AGG(pt.tags_id::TEXT,',') AS agg
FROM posts p
JOIN posts_tags pt ON pt.posts_id = p.id
GROUP BY pt.posts_id, p.content
)
SELECT post,content
FROM j
WHERE STRING_TO_ARRAY(agg,',') @> ('{2,1}'::TEXT[])

edited Feb 22 '18 at 19:53

answered Feb 22 '18 at 17:24

Jim Jones

18,404
3
35
44

I may have been unclear. This will also select posts that have only one of the tags. – schoel Feb 22 '18 at 17:30
Just added a second option with `OR` and `AND` to my answer – Jim Jones Feb 22 '18 at 17:34
I think OR will be functionally equivalent to the IN example and the AND will never return any rows since no rows have multiple tag ids. – schoel Feb 22 '18 at 18:20
Last try.. I just added an example using CTE :-) – Jim Jones Feb 22 '18 at 19:52

score 0 · Accepted Answer · answered Feb 22 '18 at 19:41

0

Found an answer to my own question:

SELECT posts.*
FROM posts
INNER JOIN posts_tags ON posts_tags.posts_id = posts.id
INNER JOIN tags ON tags.id = posts_tags.tags_id
WHERE tags.id IN (1, 2)
GROUP BY posts.id
HAVING COUNT(*) > 1

answered Feb 22 '18 at 19:41

schoel

793
1
6
14

Select rows which has multiple values in junction table

2 Answers2