Question 315645 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgre SQL: Add a column that is a level above the related column

The situation is this:
I have two columns in my database.

User Parent
Amy K. Theo K.
Lena K. Theo K.
Jiha L. John L.
John L. Victor L.
Theo K. Simba K.
Simba K. Ellen K.

Now I need to make another column using PostgreSQL called Grandparent where I need to put users from the User column in the Grandparent column if they are Parents of the people in the Parent column. So it would look like this:

User Parent Grandparent
Amy K. Theo K. Simba K.
Lena K. Theo K. Simba K.
Jiha L. John L. Victor L.
John L. Victor L. null
Theo K. Simba K. Ellen K.
Simba K. Ellen K. null

I need to do it directly from the database using SQL and save data in Power BI.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found the solution:

    The table should be self-joined:

    SELECT t.User, t.Parent, th.Parent AS Grandparent

FROM   table t

LEFT JOIN   table th on t.Parent = th.User

    Link as a reference: SQL query to get the employee name and their manager name from the same table


  2. 0

    You can self-join your table using LEFT JOIN to get grandparent (null if not exists).

    This query fetches the grandparent from the table by joining it with itself twice. The table is aliased as p (for Parent), and gp (for Grandparent). The LEFT JOIN statements connect Parent to Grandparent based on column Parent from first alias and User from second alias :

    SELECT p.User_name, p.Parent, gp.Parent AS Grandparent 
FROM mytable p
LEFT JOIN mytable gp on p.Parent = gp.User_name

    Results :

    user_name   parent      grandparent
Amy K.      Theo K.     Simba K.
Lena K.     Theo K.     Simba K.
Jiha L.     John L.     Victor L.
John L.     Victor L.   null
Theo K.     Simba K.    Ellen K.
Simba K.    Ellen K.    null

    Demo here

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

Back To Top
Search