1

In my PostgreSQL database I have table with such structure:

| organization_id | organization_name | parent_organization_id | tree_organization_id |
|-----------------|-------------------|------------------------|----------------------|
| 1               | Alphabet          |                        |                      |
| 2               | Google            | 1                      | \1\2                 |
| 3               | Google X          | 2                      | \1\2\3               |

As you can see, the table stores a hierarchical relationship between organizations. Let's say as input I have an array of ids. For simplicity, let's say I have the following array [3]. How do I get a list of all parent organizations from an array? In my case, the final result which I want to see is this:

| organization_id | organization_name | parent_organization_id | tree_organization_id |
|-----------------|-------------------|------------------------|----------------------|
| 1               | Alphabet          |                        |                      |
| 2               | Google            | 1                      | \1\2                 |

Do I need to focus on the tree_organization_id column or recursively iterate over the parent_organization_id column?

PostgreSQL version:

PostgreSQL 11.4 (Debian 11.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

Columns types:

| Column name            | Column type |
|------------------------|-------------|
| organization_id        | int8        |
| organization_name      | varchar     |
| parent_organization_id | int8        |
| tree_organization_id   | varchar     |
Nurzhan Nogerbek
  • 4,806
  • 16
  • 87
  • 193
  • What are the types of your columns? What do you expect as output if the input array is longer? And most importantly, what have you tried so far? – petre Feb 11 '20 at 05:25
  • I updated my post with information about columns types. The max length of the input array can be 2000. The min length of the input array can be 1. – Nurzhan Nogerbek Feb 11 '20 at 06:15

2 Answers2

2

Use Common Table Expression(CTE) to list all the parent or children relationship. With the help of CTE, you can eliminate the tree_organization_id column.

The link provides a very good tutorial for CTE beginners.

Thirumal
  • 8,280
  • 11
  • 53
  • 103
0

it's either way you can follow, i would prefer to recursively iterate over parent_organisation_id.

here is the Query Below.

select * from company where
    organization_id in( select parent_organization_id
                       from company where parent_organization_id is not null);

you can find the working example from here

Ullas Sharma
  • 450
  • 11
  • 22
  • Thank you for your replay but your code is not applicable if there is more data in the table and I want to know all the parents of certain organizations. I assume that in the subquery you need to apply the array I mentioned in my post. Do you have any other ideas? – Nurzhan Nogerbek Feb 11 '20 at 06:30