Re: Select rows when all all ids of its children records matches

Hi Rob, I figured it out. thanks. It is giving correct data. > Aren't you looking for cte.tags = array[1,2]? posts_tags_cte has tags column, so I am using it. Thanks, Arup Rakshit a...@zeit.io > On 12-Sep-2018, at 9:47 PM, Rob Sargent wrote: > > > > On 09/12/2018 10:08 AM, Arup Rakshit

Re: Select rows when all all ids of its children records matches

On 09/12/2018 10:08 AM, Arup Rakshit wrote: I tried : WITH posts_tags_cte AS (         SELECT post_id, array_agg(tag_id) as tags         FROM posts_tags         WHERE tag_id in (1, 2)         GROUP BY post_id ) SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id

Re: Select rows when all all ids of its children records matches

I tried : WITH posts_tags_cte AS ( SELECT post_id, array_agg(tag_id) as tags FROM posts_tags WHERE tag_id in (1, 2) GROUP BY post_id ) SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id = posts_tags_cte.post_id WHERE posts_tags_cte.tags @> array[1, 2]::int8

Re: Select rows when all all ids of its children records matches

> On 12 Sep 2018, at 17:44, Arup Rakshit wrote: > > Can you show me the SQL construction? Do I need to use `WITH`? An option is to create a bit-wise OR and SUM the grouped results. If the result of these 3 bits is 7, than the post matches all three tags. select p.id, p.name from post p

Re: Select rows when all all ids of its children records matches

Can you show me the SQL construction? Do I need to use `WITH`? Thanks, Arup Rakshit a...@zeit.io > On 12-Sep-2018, at 9:13 PM, David G. Johnston > wrote: > > On Wednesday, September 12, 2018, Arup Rakshit > wrote: > IN is OR, I want the AND logic. Select posts which h

Re: Select rows when all all ids of its children records matches

On Wednesday, September 12, 2018, Arup Rakshit wrote: > IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( > tag ids ) > Build arrays and then use the “contains” operator. David J.

Re: Select rows when all all ids of its children records matches

IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( tag ids ) Thanks, Arup Rakshit a...@zeit.io > On 12-Sep-2018, at 8:58 PM, Ron wrote: > > Maybe this: > select p.id, p.name > from posts p, > posts_tags pt, > tags t > where t.id in (1, 2, 3) > and t.id = pt

Re: Select rows when all all ids of its children records matches

Maybe this: select p.id, p.name from posts p, posts_tags pt, tags t where t.id in (1, 2, 3)   and t.id = pt.tag_id   and pt.post_id = p.id; On 09/12/2018 10:23 AM, Arup Rakshit wrote: I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, name) ... I want to get all

Select rows when all all ids of its children records matches

I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, name) ... I want to get all posts which has tag id 1, 2 and 3 for example. How should I do this? I tried ALL, but it didn’t work. Those tag ids comes from UI by users, so I am looking for generic approach. Thanks, Arup