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