sql - Request that handle unlimited parent-CodePudding

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.

CodePudding user response：

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