Home > Software design >  Get rows of parents without childs
Get rows of parents without childs

Time:06-05

I have this table:

UID CHILD_UID
a {}
b {}
c {a}

UID is a VARCHAR and CHILD_UID is a VARCHAR[]. I need a query that returns b,c. I mean a list of parents without childs checking that the uid is not in any of the child_uid rows.

  • a is not a parent, is a child of c.
  • b is a parent.
  • c is a parent.

I have tried this but with no results:

SELECT * FROM table WHERE NOT uid = ANY(SELECT child_uid FROM table);

In words, get the items that it's uid is not in the child_uid field.

CodePudding user response:

child_uid is an array, so it should be converted to a list of childs (using UNNEST)

SELECT * FROM table 
   WHERE NOT uid = ANY(SELECT unnest(child_uid) FROM table);

CodePudding user response:

You can use a LEFT SELF JOIN where you check whether the parent is not found as a child:

SELECT parents.UID
FROM       tab parents
LEFT JOIN tab children
       ON parents.UID = ANY(children.CHILD_UID)
WHERE children.UID IS NULL

Try it here.

  • Related