CODEWITHSUNDEEP
sqliteselectjoinwhere-clause
Sergey Grischenko
Sergey Grischenko

Reputation: 31

Sqlite join tables and select where column contains

I have 3 tables

**Posts**

id post_title
1  First_Post
2  Second_Post
3  Third_Post


**Tags**

id tag_name
1  Published
2  Favorites
3  Deleted


**PostTagRelatives** 

id post_id tag_id
1    1       1
2    1       2
3    2       3

I use query 

SELECT p.*, GROUP_CONCAT(PostTagRel.tag_id) AS tags FROM Posts p left
join PostTagRelatives PostTagRel on PostTagRel.post_id = p.id GROUP BY
p.id

and it's works fine.

What I need to add to sql query to get only posts that contains both tags "Published" and "Favorites". I tried insert before GROUP BY some condition like 

WHERE (',' || tags || ',') LIKE '%,1,2,%'

but it didn't help.

Upvotes: 1

Views: 297

Answers (1)

woot
woot

Reputation: 7606

You could try a subselect to look for both. I'm assuming a PK and that a post can't have the same tag more than once.

SELECT p.*, GROUP_CONCAT(PostTagRel.tag_id) AS tags 
FROM Posts p 
left join PostTagRelatives PostTagRel on PostTagRel.post_id = p.id 
WHERE 2 = ( SELECT COUNT(*)
            FROM PostTagRelatives PTR
            INNER JOIN Tags T ON (T.tag_id = PTR.tag_id AND PTR.post_id = p.post_id )
            WHERE T.tag_name IN ('Published','Favorites') )
GROUP BY p.id

Upvotes: 1

Related Questions