Join foreign key on the same table and show parent and child

user_6041389 0
Viewed 130

I have a foreign key which points on the primary key of the same table (child - parent).

I am trying to write a query that will bring the results in the form as shown below:

Parent 1 
Parent 1 > Child 1
Parent 1 > Child 2
Parent 2 
Parent 2 > Child 2 
Parent 3 
Parent 4

I thought of left joining the table, even though I am very close for it, I haven't been able to do it. Let me know what I am doing wrong or if there is an easier solution on this.

SELECT 
    a.OrganizationTypeID,
    b.ParentOrganizationTypeID,
    a.Name ParentName,
    b.Name ChildName,
    CASE
        WHEN b.ParentOrganizationTypeID IS NULL THEN a.Name
        ELSE CONCAT_WS('>', a.Name, b.Name)
    END AS res
FROM
    tblOrganizationTypes a
        left JOIN
    tblOrganizationTypes b ON b.ParentOrganizationTypeID = a.OrganizationTypeID
WHERE a.DateDeleted is null
GROUP BY b.OrganizationTypeID
ORDER BY a.OrganizationTypeID , b.ParentOrganizationTypeID

enter image description here

enter image description here

self-join mysql sql
2 Answers

Please remove Group by clause from your query.

SELECT 
    a.OrganizationTypeID,
    b.OrganizationTypeID,
    
    a.Name ParentName,
    b.Name ChildName,
    CASE
        WHEN b.ParentOrganizationTypeID IS NULL THEN a.Name
        ELSE CONCAT_WS('>', a.Name, b.Name)
    END AS res
FROM
    tblOrganizationTypes a
    left join    
    tblOrganizationTypes b on a.OrganizationTypeID=b.ParentOrganizationTypeID 
    or (a.ParentOrganizationTypeID is null and a.OrganizationTypeID=b.OrganizationTypeID)
where a.DateDeleted is null and b.Name is not null
ORDER BY a.ParentOrganizationTypeID , b.OrganizationTypeID

DB-Fiddle:

Schema and insert statements:

 create table tblOrganizationTypes (OrganizationTypeID int, ParentOrganizationTypeID int, Name varchar(100),DateDeleted date);
 insert into tblOrganizationTypes values(1,null, 'parent',null);
 insert into tblOrganizationTypes values(2,1, 'child 1',null);
 insert into tblOrganizationTypes values(3,1, 'child 2',null);
 insert into tblOrganizationTypes values(5,null, 'parent 5',null);

Query:

 SELECT 
     a.OrganizationTypeID,
     b.OrganizationTypeID,
     
     a.Name ParentName,
     b.Name ChildName,
     CASE
         WHEN b.ParentOrganizationTypeID IS NULL THEN a.Name
         ELSE CONCAT_WS('>', a.Name, b.Name)
     END AS res
 FROM
     tblOrganizationTypes a
     left join    
     tblOrganizationTypes b on a.OrganizationTypeID=b.ParentOrganizationTypeID 
     or (a.ParentOrganizationTypeID is null and a.OrganizationTypeID=b.OrganizationTypeID)
 where a.DateDeleted is null and b.Name is not null
 ORDER BY a.ParentOrganizationTypeID , b.OrganizationTypeID

Output:

OrganizationTypeID OrganizationTypeID ParentName ChildName res
1 1 parent parent parent
1 2 parent child 1 parent>child 1
1 3 parent child 2 parent>child 2
5 5 parent 5 parent 5 parent 5

db<>fiddle here

user_86516010

I suggest you split the two cases:

-- roots
select a.OrganizationTypeID
    , a.ParentOrganizationTypeID
    , a.Name
    , a.Name as childname
    , a.Name as res
from tblOrganizationTypes a
where a.ParentOrganizationTypeID is null
  and a.DateDeleted is null 
union all
-- children of roots
SELECT 
    a.OrganizationTypeID,
    b.OrganizationTypeID,
    a.Name ParentName,
    b.Name ChildName,
    CONCAT_WS('>', a.Name, b.Name) AS res
FROM tblOrganizationTypes a
JOIN tblOrganizationTypes b 
    ON b.ParentOrganizationTypeID = a.OrganizationTypeID 
where a.DateDeleted is null 
ORDER BY OrganizationTypeID, ParentOrganizationTypeID;

You can easily transform it into a recursive CTE if you want deeper nesting

I modified your Fiddle

user_35923960
Related