SQLTeam.com | Weblogs | Forums

SQL Server Nested Set Hierarchy


#1

Hi Guys,

I need a bit of help on how to create Nested Set Model - hierarchy tree from the tables below.

Currently, I am using the T-SQL below to query the tables to find the Master and the related Children
Select * From #ClientGroups AS CG
Inner join #Customers AS C
ON C.CustomerKey >= CG.GroupFromCustKey
AND C.CustomerKey <= CG.GroupToCustKey

This approached is not suited for our needs.
The [AssociatedClient] is a free text which highlight related clients but, because it is free text it can go wrong.

My question is how can this two tables be modelled to enable Nested Set Model to deal with hierarchical data.

Can I have something like:
Id, ClientID1, ClientID2, ClientID3,ReasonType

CREATE TABLE #ClientGroups(
[GroupID] [int] IDENTITY(1,1) NOT NULL, -- PrimaryKey
[GroupMasterCustKey] varchar NOT NULL,
[GroupFromCustKey] varchar NOT NULL, --- Customer Range from
[GroupToCustKey] varchar NOT NULL --- Customer Range to
)

CREATE TABLE #Customers(
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerKey] varchar NOT NULL, --- PrimaryKey
[CreatedBy] varchar NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[ModifiedBy] varchar NULL,
[ModifiedOn] [datetime] NULL,
[CompanyName] varchar NULL,
[AssociatedClient] varchar NULL
)


#2

Please provide sample data in the form of insert statements and desire output


#3

Hi Jotorre,

Thanks for coming back. See the test data below

Definitions:
Parent & child relationship currently captured in the tblCGs table. (Linked accounts – these are hierarchical)
• Currently a tblCGs table allows the linking of Parent(GroupMasterCustKey) to child (CustomerKey) which may or may not exist
• This allows access between a parent and children relationship and access to shared agreements between them.
• Clients that part of the same group, the child clients don’t have their own agreements.

AssociatedClient Field: These are related clients without a hierarchical relationship.
• Currently a field on the tblCs table is too limiting and
• Clients that are related for non-SLA reasons, each client has their own agreement
• Can have implications with service retention or other issues.

Solution:

  1. To Implement a new structure where individual rows such as MIND227 - MIND227 are retrieved from the db rather than IND227 - IND229
    2.Change the structure of the tbcg
    3.Move the capture of Associatedclients Field into a separate table [ID, ClientID1 , ClientID2..... , ClientRelationshipReasonID, CreatedDate]

What I should have should like:
ID ClientID1 ClientID2 ReasonTypeID
1 201 301 1
2 201 302 1
3 201 303 1
4 303 401 1
5 601 701 3

IF OBJECT_ID('tempdb..#tblCGs') IS NOT NULL DROP TABLE #tblCGs
IF OBJECT_ID('tempdb..#tblCs') IS NOT NULL DROP TABLE #tblCs

--Create ClientGroups table.
CREATE TABLE #tblCGs
(
[GroupID] int NOT NULL, ---- IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[GroupMasterCustKey] varchar(100) NOT NULL, ---[MasterCAN]
[GroupFromCustKey] varchar(100) NOT NULL, --- CONSTRAINT [DF_tblCGs_GroupFromCustKey] DEFAULT (' '),
[GroupToCustKey] varchar(100) NOT NULL -- CONSTRAINT [DF_tblCGs_GroupToCustKey] DEFAULT (' '),
)

-- Populate the table with values.
INSERT INTO #tblCGs VALUES
(988, N'MIN036', N'MIND227', N'MIND229')
, (668, N'08035635', N'31036422', N'31036422')
,(669, N'08035635', N'31035623', N'31035623')

-- Create Customer table.
CREATE TABLE #tblCs ---Customers
([CustomerID] int not null ------IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
,[CustomerKey] varchar(100) NOT NULL --- [ChildCAN]
,[CompanyName] varchar(300) NULL
,[AssociatedClient] varchar(20)
,[CreatedOn] Datetime
)

--Populate the table with values.
INSERT INTO #tblCs VALUES
(72894, N'MIND227', N'MIND Mind in Haringey', N'MIN036','2011-11-16 00:00:00.000')
,(93469, N'MIND228', N'Caerphilly Borough Mind', N'MIN036','2014-03-08 00:00:00.000')
,(98199, N'MIND229', N'Brecon and District Mind', N'MIN036,BDC004,POW044','2014-11-26 00:00:00.000')
,(61705, N'31036422', N'Revive', N'Null','2010-04-01 09:13:10.550')
,(61729, N'31035623', N'Jysk Ltd', N'Null','2010-04-01 12:56:16.837')


--- Current Queries
SELECT *
FROM #tblCGs AS CG
INNER JOIN #tblCs AS C
ON C.CustomerKey >= CG.GroupFromCustKey
AND C.CustomerKey <= CG.GroupToCustKey

This is just an example desired result. Demoralization of parts of the hierarch - Like Adjacency list ornested sets I think

Thanks

Ziggy


#4

Hi Jotorre,

I have two tables at the moment.
tblCGs table and #tblCs.

The tblCGs is a lookup table to get GroupMasterCustomer key with associated
clients in tblCs table. However, the tblCGs keys are in range,
like GroupFromCustKey to CG.GroupToCustKey. I don't want the range keys
but, individual row per record or move all the associated client into
individual table.

Currently, I queried the tables using the script below to get the parent
and the children which are one direction only.

--- Current Queries
SELECT *
FROM #tblCGs AS CG
INNER JOIN #tblCs AS C
ON C.CustomerKey >= CG.GroupFromCustKey
AND C.CustomerKey <= CG.GroupToCustKey

How can I restructure the table to accommodate to look like this
ID ClientID1 ClientID2 ReasonTypeID
1 201 301 1
2 201 302 1
3 201 303 1
4 303 401 1
5 601 701 3

and not customerkey from MIND227 - MIND227. Please see the sample data
below.

IF OBJECT_ID('tempdb..#tblCGs') IS NOT NULL DROP TABLE #tblCGs
IF OBJECT_ID('tempdb..#tblCs') IS NOT NULL DROP TABLE #tblCs

--Create ClientGroups table.
CREATE TABLE #tblCGs
(
[GroupID] int NOT NULL, ---- IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[GroupMasterCustKey] varchar(100) NOT NULL, ---[MasterCAN]
[GroupFromCustKey] varchar(100) NOT NULL, --- CONSTRAINT
[DF_tblCGs_GroupFromCustKey] DEFAULT (' '),
[GroupToCustKey] varchar(100) NOT NULL -- CONSTRAINT
[DF_tblCGs_GroupToCustKey] DEFAULT (' '),
)

-- Populate the table with values.
INSERT INTO #tblCGs VALUES
(988, N'MIN036', N'MIND227', N'MIND229')
, (668, N'08035635', N'31036422', N'31036422')
,(669, N'08035635', N'31035623', N'31035623')

-- Create Customer table.
CREATE TABLE #tblCs ---Customers
([CustomerID] int not null ------IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
,[CustomerKey] varchar(100) NOT NULL --- [ChildCAN]
,[CompanyName] varchar(300) NULL
,[AssociatedClient] varchar(20)
,[CreatedOn] Datetime
)

--Populate the table with values.
INSERT INTO #tblCs VALUES
(72894, N'MIND227', N'MIND Mind in Haringey', N'MIN036','2011-11-16
00:00:00.000')
,(93469, N'MIND228', N'Caerphilly Borough Mind', N'MIN036','2014-03-08
00:00:00.000')
,(98199, N'MIND229', N'Brecon and District Mind',
N'MIN036,BDC004,POW044','2014-11-26 00:00:00.000')
,(61705, N'31036422', N'Revive', N'Null','2010-04-01 09:13:10.550')
,(61729, N'31035623', N'Jysk Ltd', N'Null','2010-04-01 12:56:16.837')