Debian Question posted in
The oficial documentation can be found here.

How do I get related data in PostgreSQL table? – Debian

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                      | 12                 |
| 3               | Google X          | 2                      | 123               |

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                      | 12                 |

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     |
Tags:

2

Answers


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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search