Postgresql – Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation

I'm not great with recursion either, so don't feel bad. I've had success with structures similar to the following. It's not elegant, but you can see exactly what's going on. Basically you need to gather all the possible values to evaluate against both ID and ParentID for a given ParentID value. This solution assumes integer values. It will get a little more tricky with alphanumric values. The nice thing about this is you can run it as is or modify it to display a hierarchical ordering.

    --create the table for the example
    IF OBJECT_ID('tbl_RecursiveExample','U') IS NOT NULL
        DROP TABLE tbl_RecursiveExample

    CREATE TABLE tbl_RecursiveExample
    (
          ID INT
        , ParentID INT
    )

    INSERT tbl_RecursiveExample
    VALUES   (1,NULL)
           , (2,1)
           , (3,1)
           , (5,1)
           , (5,4)
           , (5,6)
           , (8,6)
           , (7,9)
           , (9,10);


    --change the value to the parentID you are interested in
    DECLARE @ParentIDVal INT=10;

    WITH 
    cteFirstRelationship --Top level relationships
    AS
    (
        SELECT ID RelatedIDs
        FROM tbl_RecursiveExample 
        WHERE ParentID = @ParentIDVal
        UNION
        SELECT ParentID
        FROM tbl_RecursiveExample
        WHERE ID = @ParentIDVal
    ),
    cteSecondRelationship --secondary relationships
    AS
    (
        SELECT ID RelatedIDs
        FROM tbl_RecursiveExample t
            JOIN cteFirstRelationship cf ON cf.RelatedIDs=t.ParentID
        UNION
        SELECT ParentID
        FROM tbl_RecursiveExample t
            JOIN cteFirstRelationship cf ON cf.RelatedIDs=t.ID
    )
    --possible add another level if needed
    SELECT DISTINCT t.* --> Needed due to the match of both values
    FROM tbl_RecursiveExample t
        JOIN
        (       
            SELECT RelatedIDs from cteFirstRelationship
            UNION
            SELECT RelatedIDs from cteSecondRelationship
        )r ON r.RelatedIDs = t.ID OR r.RelatedIDs = t.ParentID

Yep! I finally solved my problem..

I Hope this help to somebody..

drop procedure if exists sp_RecursiveUser;

delimiter #

create procedure sp_RecursiveUser
(
in prm_UserID  int unsigned
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

create temporary table hier(
 parent_user_id smallint unsigned, 
 user_id smallint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier select ParentUserID, Id, v_depth from systemuser where Id = prm_UserID ;

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from systemuser p inner join hier on p.ParentUserID = hier.user_id and hier.depth = v_depth) then

        insert into hier 
            select p.ParentUserID, p.Id, v_depth + 1 from systemuser p 
            inner join tmp on p.ParentUserID = tmp.user_id and tmp.depth = v_depth;

        set v_depth = v_depth + 1;          

        truncate table tmp;
        insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

select 
 p.Id,
 p.UserName as category_name,
 b.Id as parent_user_id,
 b.UserName as parent_category_name,
 hier.depth
from 
 hier
inner join systemuser p on hier.user_id = p.Id
left outer join systemuser b on hier.parent_user_id = b.Id
order by
 hier.depth, hier.user_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #