CODEWITHSUNDEEP
sqldatabasepostgresqljoinrecursive-query
Ilesh Patel
Ilesh Patel

Reputation: 2155

Select data from child parent relationship of the same table

I have below table schema:

create table location_master (id serial,name character varying,parent_id integer);

INSERT INTO location_master (name,parent_id)
VALUES
   ('Bombay',1)
 , ('Ahmedabad',1)
 , ('Surat',1)
 , ('Vapi',3)
 , ('Navarangpura',2);

location_master

From above table I want to find the path from source id = 1 to destination id = 4. So the expected result is as below:

Expected outcome

Here destination id = 4. Which has parent_id = 3. On words id = 3 has parent_id = 1 and so on.

Example:
A user wants to send a parcel from id = 1 to id = 4. Routing path for the parcel is decided by this table. When a user selects source id = 1 and destination id = 4 then the query automatically finds the route from child-parent relationship. Parcel will go through 1,3,4 path.

I tried SELF JOIN for this but did not get the expected result.

Upvotes: 0

Views: 1558

Answers (1)

Ilesh Patel
Ilesh Patel

Reputation: 2155

Thanks joop. I search for Recursive Query and got the solution as I want:

    WITH RECURSIVE demo AS (
    SELECT parent_id      
    FROM location_master                   
    WHERE id = <destination_id>
    UNION ALL
    SELECT a.parent_id               
    FROM location_master a                 
    JOIN demo b ON(a.id = b.parent_id))
    SELECT DISTINCT location_master.* FROM location_master JOIN demo 
           on location_master.id = demo.parent_id WHERE demo.parent_id != 0 
    UNION 
    SELECT location_master.* FROM location_master WHERE id = <destination_id> 
    ORDER BY id  ;

Upvotes: 1

Related Questions