2

I have a table with Parent-Child Relation in the same Table. Some Sample Data

  id │ name                 │ parent │ is_active  
 ════╪══════════════════════╪════════╪═══════════ 
  1  │ Company A            │ null   │ true       
  2  │ Child of A           │ 1      │ true       
  3  │ 2Child of A          │ 1      │ true       
  4  │ 3Child of A          │ 1      │ true       
  5  │ 0 Single Company     │ null   │ true       
  6  │ 1 Single Company     │ null   │ true       
  7  │ 2 Single Company     │ null   │ true       
  8  │ Parent Company B     │ null   │ true       
  9  │ 0 Child Company of B │ 8      │ true       
  10 │ 1 Child Company of B │ 8      │ true       
  11 │ 2 Child Company of B │ 8      │ true       
  12 │ 3 Child Company of B │ 8      │ true   

I want to write two queries

  1. get all the parent records (only parent records) Example: id 1,8

  2. Get all the single records (only records does not come under parent-child) Example: 5,6,7

Please help to write select queries.

0
SELECT t1.*
FROM "table" t1
WHERE t1.parent IS NULL
  AND /* NOT */ EXISTS (SELECT 1
                        FROM "table" t2
                        WHERE t1.id = t2.parent)

EXISTS - for parent records.

NOT EXISTS - for single records.

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.