Display Parent-Child relationship when Parent and Child are stored in same table

Question

I have SQL Server table structure like below:

ID    Name     ParentID
-----------------------
1     Root       NULL
2     Business   1
3     Finance    1
4     Stock      3

I want to display the details in my web page like

ID    Name     ParentName
-------------------------
1     Root      -
2     Business  Root
3     Finance   Root
4     Stock     Finance    

How can I construct my SQL query?

Answer 1

try this...

SELECT a.ID, a.Name, b.Name AS 'ParentName'
FROM TABLE AS a LEFT JOIN TABLE AS b on a.ParentID = b.ID

With the left join, the query will not find anything to join for the NULL and return blank for the ParentName column.

EDIT:

If you do not want the 'Parent' column to be blank, but want to show a '-' dash then use this query.

SELECT a.ID, a.Name, COALESCE(b.Name,'-') AS 'ParentName'
FROM TABLE AS a LEFT JOIN TABLE AS b on a.ParentID = b.ID

Answer 2

Assuming SQL Server 2005+, use a recursive CTE like this:

WITH hierarchy AS (
  SELECT t.id,
         t.name,
         t.parentid,
         CAST(NULL AS VARCHAR(50)) AS parentname
    FROM YOUR_TABLE t
   WHERE t.parentid IS NULL
  UNION ALL
  SELECT x.id,
         x.name,
         x.parentid,
         y.name
    FROM YOUR_TABLE x
    JOIN hierarchy y ON y.id = x.parentid)
SELECT s.id,
       s.name,
       s.parentname
  FROM hierarchy s

The CASTing of the NULL might look odd, but SQL Server defaults the data type to INT unless specified in a manner like you see in my query.

Answer 3

I am facing same situation. I want to fetch all child list of particular parent from same table, where as MySQL is not provided Recursive CTE in below MySQL 8.

I had resolved my issue with recursive store procedure. In that we need to set max_sp_recursion_depth to recursive store procedure call.

My table structure is below

My store procedure(With recursion) is below:

DROP PROCEDURE IF EXISTS getAllChilds;
DELIMITER $$
SET @@SESSION.max_sp_recursion_depth=25; $$
CREATE PROCEDURE getAllChilds (IN inId int(11), INOUT list_obj text, IN end_arr CHAR(1))
BEGIN
    DECLARE stop_cur INTEGER DEFAULT 0;
    DECLARE _id INTEGER DEFAULT 0;
    DECLARE _name VARCHAR(20) DEFAULT 0;
    DEClARE curSelfId CURSOR FOR SELECT id, name FROM new_table where parentId = inId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop_cur = 1; 
    OPEN curSelfId; 
    getSelfId: LOOP
        FETCH curSelfId INTO _id, _name;
        IF stop_cur = 1 THEN LEAVE getSelfId; END IF;
        IF list_obj is null or list_obj = "" then 
            SET list_obj = CONCAT("[","{",'"name":"',_name,'","id":"',_id,'"',"}");
        else 
            SET list_obj = CONCAT(list_obj,',', "{",'"name":"',_name,'","id":"',_id,'"',"}");
        end if;
        CALL getAllChilds(_id,list_obj,"");
    END LOOP getSelfId;
    CLOSE curSelfId;
    IF end_arr is not null and end_arr != "" then 
        SET list_obj = CONCAT(list_obj,end_arr);
        SELECT @ids;
    end if;
END$$
DELIMITER ;

To Call this stored procedure we need to pass 3 arguments,

1. Parent id
2. Empty/null object

3. End array sign.(for append only last object instead of all objects).

   CALL getAllChilds(1,@ids,']');

Using this store procedure you may get all level child in JSON string. You can parse this json string and convert in any OBJECT using any JSONparser.

Or

we can wrap this answer in store procedure for use it into any programming language like JAVA. we are wrapping this into store procedure because we can't use := in query. Here we are using find_in_set function. My store procedure(Without recursion).

DROP PROCEDURE IF EXISTS getAllChilds;
DELIMITER $$
CREATE PROCEDURE getAllChilds (IN inId int(11))
BEGIN
    select id,
        name,
        parentid
    from  
        (select * from new_table
        order by parentid, id) new_table,
        (select @pv := inId) initialisation
    where   
        find_in_set(parentid, @pv) > 0
        and @pv := concat(@pv, ',', id);
END$$
DELIMITER ;

To call this store procedure we need just parent Id.

CALL getAllChilds(1);

Answer 4

SELECT CH.ID, CH.NAME, ISNULL(PA.NAME, '-') AS "PARENTNAME"
FROM TBL CH
LEFT OUTER JOIN TBL PA
ON CH.PARENTID = PA.ID

Answer 5

I think the following query would work. I've not tested it.

SELECT
ID
, name
, (CASE WHEN parent_name IS NULL THEN '-' ELSE parent_name END)
FROM
RELATIONS
, (SELECT 
parentID
, name AS parent_name
FROM
RELATION) PARENT
WHERE
RELATIONS.parentId = PARENT.parentId

Basically, what I'm doing is doing is choosing parent information, and relating it to each tuple.

Answer 6

Like someone posted earlier, it needs to be a recursive function. There can be multiple scenarios - One Parent to Multiple Child (Level 1 Hierarchy) Parent 1 has Child 1 Parent 1 has Child 2 Child to Multiple Child (Level 2 Hierarchy) And so on.

My example has parent curve id and child curve id. For example,

WITH hierarchy AS
(select UT.parent_curve_id as origin, UT.*
from myTable UT
WHERE UT.parent_curve_id IN ( 1027455, 555)
UNION ALL
select h.origin, UT.*
from myTable UT
JOIN hierarchy h ON h.child_curve_id = UT.parent_curve_id
)
SELECT *
from hierarchy 
order by unique_key

Answer 7

This is a simple way:

SELECT ID,Name,(SELECT TOP 1 Name FROM Table t WHERE t.ParentID=ParentID) AS ParentName FROM Table

Answer 8

For Mysql just incase someone needs

WITH RECURSIVE hierarchy AS (
  SELECT t.id,
         t.name,
         t.parentid,
         CAST(NULL AS VARCHAR(50)) AS parentname
    FROM YOUR_TABLE t
   WHERE t.parentid IS NULL
  UNION ALL
  SELECT x.id,
         x.name,
         x.parentid,
         y.name
    FROM YOUR_TABLE x
    JOIN hierarchy y ON y.id = x.parentid)
SELECT s.id,
       s.name,
       s.parentname
  FROM hierarchy s