3

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.

2 Answers 2

1
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.

0
0

If possible can you please elaborate your question, also write your Select queries for better understanding.

as you said "Parent-Child Relation in the same Table", do you mean to return all parent values as distinct?

Then,

  1. get all the parent records (only parent records) Example: id 1,8
SELECT DISTINCT parent
FROM "table_name" t1
WHERE parent IS NOT NULL;
  1. Get all the single records (only records does not come under parent-child) Example: 5,6,7
SELECT DISTINCT id
FROM "table_name" t1
WHERE t1.id= t1.parent IS NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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