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
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