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[]

But it gives me all the posts.

Thanks,

Arup Rakshit
ar(at)zeit(dot)io

> On 12-Sep-2018, at 9:14 PM, Arup Rakshit <ar(at)zeit(dot)io> wrote:
>
> Can you show me the SQL construction? Do I need to use `WITH`?
>
>
> Thanks,
>
> Arup Rakshit
> ar(at)zeit(dot)io <mailto:ar(at)zeit(dot)io>
>
>
>
>> On 12-Sep-2018, at 9:13 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>>
>> On Wednesday, September 12, 2018, Arup Rakshit <ar(at)zeit(dot)io <mailto:ar(at)zeit(dot)io>> 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.
>