Postgres Pro
Downloads
Home   >   mailing lists

Re: Select rows when all all ids of its children records matches - Mailing list pgsql-general

From Arup Rakshit
Subject Re: Select rows when all all ids of its children records matches
Date
Msg-id F791B2E6-5D6D-44E0-B593-96E478B34A55@zeit.io
Whole thread Raw
In response to Re: Select rows when all all ids of its children records matches  (Arup Rakshit <ar@zeit.io>)
Responses Re: Select rows when all all ids of its children records matches  (Rob Sargent <robjsargent@gmail.com>)
List pgsql-general
Tree view
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



On 12-Sep-2018, at 9:14 PM, Arup Rakshit <ar@zeit.io> wrote:

Can you show me the SQL construction? Do I need to use `WITH`?


Thanks,

Arup Rakshit



On 12-Sep-2018, at 9:13 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wednesday, September 12, 2018, Arup Rakshit <ar@zeit.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. 


pgsql-general by date:

Previous
From: Alban Hertroys
Date:
Subject: Re: Select rows when all all ids of its children records matches
Next
From: Rob Sargent
Date:
Subject: Re: Select rows when all all ids of its children records matches