sql - Request that handle unlimited parent

Question

I am currently working on PostgreSQL having 2 data tables, LU (Logistical Unit) and Item.

An item is in a LU and a LU can be in another LU.

Item (id integer, name string, Luid integer references LU.id)
LU (id integer, name string, parentLuId references LU.id (can be null)

A a parentLuId can be null if there is no LU into it.

I want to display all the LU that contains a specific Item, even if this item is in the children of the children of this LU.

this example set of data

LU(1,"A",NULL)
LU(2,"B",NULL)
LU(3,"C",NULL)
LU(4,"D",NULL)
LU(5,"E",1)
LU(6,"F",5)


Item(1,"baloon",6)
Item(2,"baloon",2)
Item(4,"pencil",4)
Item(5,"baloon",3)
Item(6,"baloon",2)
Item(7,"baloon",6)

Selecting the UL that contains a baloon should display ABCEF because D doesnt contains a baloon. A and E contains a baloon because they are parents from F that contains baloons.

I want to be able to handle an unlimited number of parents.

Does this answer your question? [PostgreSQL recursive parent/child query](https://stackoverflow.com/questions/54907495/postgresql-recursive-parent-child-query) — Luuk, Nov 29 '21 at 09:37
I have trouble with recursivity and I am not using a table to do the association like in your example. — Pierrem, Nov 29 '21 at 10:04
OK, close request retracted.... But could you add your table definitions (DDL) and a small set of example data ? — Luuk, Nov 29 '21 at 10:05
Because you added LU.parentLuId, which has a reference to LU.id, which (can) also have another LU.parentid, which ...... you NEED to have some recursivity — Luuk, Nov 29 '21 at 14:49
Based on the text you shared above, I have created a [DBFIDDLE](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=42eb94e2657e7ce6943f8a40d4dff73c) (which outputs `ABCEF`) — Luuk, Nov 29 '21 at 14:50
@Luuk, it is based on coincidence IMHO. Item.id = lu.id is irrelevant (item.id is primary key for item table and not related to LU, no?). — Cetin Basoz, Nov 29 '21 at 14:59
@CetinBasoz: That;s why I based it on the text, not on the data he gave .... — Luuk, Nov 29 '21 at 15:36
@Luuk, I mean what if item ids started from 20. I don't see where in text he is after doing an inner join on item.id = lu.id. — Cetin Basoz, Nov 29 '21 at 15:40
@CetinBasoz: i forgot to say that you are right! But I do not know a different way of getting the requested `ABCEF` out of the given text... Maybe Pierrem will share his idea ? — Luuk, Nov 29 '21 at 15:45
@Luuk, oh it is a recursive query to get the ancestors. If what you mean is to get it pure text as ABCEF, simply sort the result on Name and use string_agg(). Check the fiddle in my reply. — Cetin Basoz, Nov 29 '21 at 15:47
@CetinBasoz: He (=OP) "I have trouble with recursivity" ... but I will mark your answer as usefull. — Luuk, Nov 29 '21 at 15:53

score 1 · Accepted Answer · answered Nov 29 '21 at 14:58

It is a recursive query with ancestors. ie:

with RECURSIVE ancestors AS (
    SELECT Id,
           Name,
           parentLuId
    FROM Lu
    WHERE exists(select * from item i where i.Luid = LU.id and i.Name = 'baloon')
    UNION
    SELECT d.Id,
           d.Name,
           d.parentLuId
    FROM Lu 
             inner JOIN ancestors c ON c.parentLuId = d.Id
)
SELECT *
FROM ancestors;

Here is DBFiddle demo

sql - Request that handle unlimited parent

1 Answers1