CODEWITHSUNDEEP
sqlsql-servert-sql
dennitorf
dennitorf

Reputation: 319

tree structure in different tables

I have four tables,

This logic allow build a tree, where the leaves are the items in Level4, and his father can take level 1, 2 or 3. In the same way, the items in Level3, can have a father that is in the 2 o 1.

There are any query to obtain the tree below for a given an id and a level, until a given level?

For example, is we have the nextdata:

Level1 - 001, GroupEnterprise1, 001, 1

Level2 - 001-1, Enterprise1, 001, 1

Level2 - 001-2, Enterprise2,001, 1

Level3 - 002-1, Enterprise3, 001-1, 2

Level4 - 003-1, Office 1, 001-1,3

Level4 - 003-2, Office 2, 001-2,3

Level4 - 003-3, Office 3, 001-2,3

Level4 - 003-4, Office 4, 001-1,3

I can want consult all the offices (items in level 4), that are are daughters, granddaughters and great-granddaughters off the group GroupEnterprise1, or the offices that are daughters of Enterprise3, o the enterprises that are daughters of GroupEnterprise1.

The parameters for query are Id, Level and Level until I wish build the tree.

Upvotes: 2

Views: 804

Answers (1)

EMUEVIL
EMUEVIL

Reputation: 512

I'm not sure I quite understand what you're trying to do. If you need a hierarchy, you should use a single table called "Levels" and have all of your Level information in that one table. You don't need 4 tables for this. You can perform a self-join to easily return parent information.

The problem is that in order to move through the chain, you would probably have to use some sort of loop. This is usually to be avoided in SQL statements because the query optimizer would have to generate an execution plan for every iteration of the loop (which is inefficient). It is possible to do with only SQL, but I recommend pulling a table with all the information, and parsing through it with a non-SQL programming language that isn't geared towards set-based operations.

Below is some sample code using a single Table for all 4 levels. Once the tree is built in the table, you just need to move through a FOR loop to display it how you want. 

--Creates Temp table (This table will expire upon connection close)
CREATE TABLE [#Levels] ([id] INT, [name] NVARCHAR(256), [level] INT, [idFather] INT);

--Populate Temp table with some sample data
INSERT INTO #Levels VALUES (1,'AbsoluteParent',1,null)
INSERT INTO #Levels VALUES (2,'ChildItem1',2,1)
INSERT INTO #Levels VALUES (3,'ChildItem2',2,1)
INSERT INTO #Levels VALUES (4,'GrandChild',3,2)

--Display populated table
SELECT * FROM [#Levels]

--Create 2 instances of our Temp table and join (id > idFather) so that we can return information about the parent table.  
SELECT [T1].[name] AS 'Name'
    , [T2].[name] AS 'Parent Name'
FROM [#Levels] AS T1
    LEFT JOIN [#Levels] T2 ON [T1].[idFather] = [T2].[id]

--We can even link another instance of our Temp table and give information about grandparents! 
SELECT [T1].[name] AS 'Name'
    , [T2].[name] AS 'Parent Name'
    , [T3].[name] AS 'Grand Parent Name' 
FROM [#Levels] AS T1
    LEFT JOIN [#Levels] T2 ON [T1].[idFather] = [T2].[id]
    LEFT JOIN [#Levels] T3 ON [T2].[idFather] = [T3].[id]

Perhaps what you are looking for is a recursive common table expression that feeds the output back into the function to display all children recursively. Here is a microsoft example: https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

I simplified the microsoft example a little:

 -- Create a temp Employee table.
CREATE TABLE #MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    ManagerID int NULL, 
);
-- Populate the table with values.
INSERT INTO #MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',273)
,(275, N'Michael', N'Blythe', N'Sales Representative',274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',285)
,(16,  N'David',N'Bradley', N'Marketing Manager',273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist',16);


WITH DirectReports (ManagerID, EmployeeID, Title, Level)
AS
(
    SELECT e.ManagerID, e.EmployeeID, e.Title, 0 AS Level
    FROM #MyEmployees AS e
    WHERE e.ManagerID is null

    UNION ALL

    SELECT e.ManagerID, e.EmployeeID, e.Title, d.Level + 1
    FROM #MyEmployees AS e
    INNER JOIN DirectReports AS d
    ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports

Upvotes: 0

Related Questions