0

I have got tables:

children_tab
id
name
lastname

parent_tab
id
name
lastname

children_parent_tab
children_id
parent_id

There is also parent view that return in field children_ids elements from children_parent_tab

I have to search parents by children ids

Let's say that parent contain:

1|name1|lastname1|{1}
2|name2|lastname2|{5}
3|name3|lastname3|{3,7}

SELECT * FROM parent p WHERE ARRAY[1,2,3,4,5]::int[]  @> children_ids

will return only parent with id 1 and 2 but without that one with id 3. How that query can be changed to return also parent with id 3 because I'm looking for parent of child with id 3?

0

You seem to run the query on the view. Should be substantially faster based on underlying tables. Like:

SELECT DISTINCT parent_id
FROM   children_parent_tab
WHERE  children_id = ANY ('{1,2,3,4,5}'::int[]);

Details depend on undisclosed exact table definitions and requirements.

Be that as it may, you can fix the query you presented with the "overlap" operator && instead of the "contains" operator @>:

SELECT * FROM parent p WHERE ARRAY[1,2,3,4,5]::int[] && children_ids;

This way you get all parents of any of the children in the given array.

  • Thanks, && made a job – Inweo Oct 15 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.