Below is what I have in my table.
myTable
++++++++++++++++++++
Parent + Child
++++++++++++++++++++
C1 + G1
C1 + G2
C1 + G3
G3 + D1
G3 + D2
C1 + G4
G4 + D3
G4 + D4
C2 + G5
C2 + G6
C2 + G7
C2 + G8
++++++++++++++++++++
What, I want is as below using MYSQL.
C1
G1
G2
G3
D1
D2
G4
D3
D4
C2
G5
G6
G7
G8
Please let me know if this is possible in MYSQL. The output is something like TREE.
Update 1
If I get new table like below is also fine so that I can use this example.
++++++++++++++++++++++++++++++++++++++++
Parent + Child + PLevel + CLevel
++++++++++++++++++++++++++++++++++++++++
C1 + G1 + 1 + 2
C1 + G2 + 1 + 2
C1 + G3 + 1 + 2
G3 + D1 + 2 + 3
G3 + D2 + 2 + 3
C1 + G4 + 1 + 2
G4 + D3 + 2 + 3
G4 + D4 + 2 + 3
C2 + G5 + 1 + 2
C2 + G6 + 1 + 2
C2 + G7 + 1 + 2
C2 + G8 + 1 + 2
++++++++++++++++++++++++++++++++++++++++
NOTE : I have started level with 1 (in example I have level starting from 0). If I get this new table with level starting from 0 is also fine.
Although you can't do with a single query, you can do with a stored procedure... The only pre-requirement, you need to add 2 more records to your existing sample table to represent that "C1" and "C2" ARE the top level... Add a record where the "Parent" field is blank, and the child level is "C1" and another for "C2". This will "prepare" the top-most parent level. for subsequent hierarchy association, otherwise you have no starting "basis" of the top-level hierarchy. It also requires a "primary key" column (which I've created in this script as "IDMyTable" which is just 1-x sequential, but would assume you have an auto-increment column on your table to use instead).
I've included all the output columns to show HOW it's built, but the premise of this routine is to create a table based on the expected column outputs, yet extra to hold the hierarchical representation downstream as it's being built. To MAKE SURE they retain the correct orientation as the layers get deeper, I'm concatinating the "ID" column -- you'll see how it works in the final result set.
Then, in the final result set, I am pre-padding spaces based on however deep the hierarchy data is.
The loop will add any records based on their parent being found in the preceding result set, but only if the ID has not already been added (prevent duplicates)...
To see how the cyclical order was constantly appended to, you can run the last query WITHOUT the order by and see how each iteration qualified and added the previous hierarchy level was applied...
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetHierarchy2`()
BEGIN
-- prepare a hierarchy level variable
set @hierlvl := 00000;
-- prepare a variable for total rows so we know when no more rows found
set @lastRowCount := 0;
-- pre-drop temp table
drop table if exists MyHierarchy;
-- now, create it as the first level you want...
-- ie: a specific top level of all "no parent" entries
-- or parameterize the function and ask for a specific "ID".
-- add extra column as flag for next set of ID's to load into this.
create table MyHierarchy as
select
t1.IDMyTable,
t1.Child AS Parent,
@hierlvl as IDHierLevel,
cast( t1.IDMyTable as char(100)) FullHierarchy
from
MyTable t1
where
t1.Parent is null
OR t1.Parent = '';
-- how many rows are we starting with at this tier level
set @lastRowCount := ROW_COUNT();
-- we need to have a "primary key", otherwise our UPDATE
-- statement will nag about an unsafe update command
alter table MyHierarchy add primary key (IDMyTable);
-- NOW, keep cycling through until we get no more records
while @lastRowCount > 0 do
-- NOW, load in all entries found from full-set NOT already processed
insert into MyHierarchy
select
t1.IDMyTable,
t1.Child as Parent,
h1.IDHierLevel +1 as IDHierLevel,
concat_ws( ',', h1.FullHierarchy, t1.IDMyTable ) as FullHierarchy
from
MyTable t1
join MyHierarchy h1
on t1.Parent = h1.Parent
left join
MyHierarchy h2
on t1.IDMyTable = h2.IDMyTable
where
h2.IDMyTable is null;
set @lastRowCount := row_count();
-- now, update the hierarchy level
set @hierLevel := @hierLevel +1;
end while;
-- return the final set now
select
*, concat( lpad( ' ', 1 + (IDHierLevel * 3 ), ' ' ), Parent ) as ShowHierarchy
from MyHierarchy
order by FullHierarchy;
END
MySQL and RDBMS's in general are not great at this sort of structure. You'll probably have to use client-side recursion to do this.
If the recursion is limited to just three deep, like your example, you can do it with joins, but it's not very scalable for deeper trees.
first Create Recursive function for calc level.
function fn_CalcLevel(int @ID)
As Begin
Declare @ParentID int
Select @ParentID = ParentID From Table1 where ID = @ID
IF (@ParentID IS NULL) Return 1 Else Return 1+fn_CalcLevel(@ParentID)
End
then Create your Query Such as Below
Select *, fn_CalcLevel(Table1.ID) as Level
From Table1
If you restructured your table a bit you could use something like:
SELECT Child,CONCAT(LPAD('',Clevel,' '),Child),etc from tablename
The restructuring is that you would need the root node in as a row with parent node of 0. You can add your own ordering with parent / child / C level to get the sequence as desired.
I know this is from a few years back, but it might save someone else some effort!
