Ordering By Child Records

if you have sql server 2005, you can do this with a single sql using the common table expression (CTE)
if you have sql server 2000 or lower, you need to implement a function/procedure to poll through the table

i have sql server 2000

JT

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm

Thanks angellIII interesting reading........

"easier using the inline table-valued User Defined Functions functionality of SQL Server 2000."

not sure how this would help my situation though of trying to group each child level for a given location ??

JT

Check out the code in Figure 5.
you have to put your tablename andfield names instead.

sorry angelIII..... getting an error when trying to create the function

testing with a sectors table

SECTORS
sectorid SMALLINT PK
sector  VARCHAR(100)
sectorparent SMALLINT

Server: Msg 213, Level 16, State 5, Procedure GetSectors, Line 7
Insert Error: Column name or number of supplied values does not match table definition.

USE INTRANET
GO

CREATE FUNCTION dbo.GetSectors(@includeparent bit, @sectorid int)
RETURNS @retFindSectors TABLE (sectorid int, sector varchar(100), sectorparent int)
AS  
BEGIN
      IF (@includeparent=1)
      BEGIN
            INSERT INTO @retFindSectors
            SELECT * FROM Sectors WHERE SectorID=@sectorid
       END

      DECLARE @Sector_ID int, @Sector_Name varchar(100), @Sector_ParentID int

      DECLARE RetrieveSectors CURSOR STATIC LOCAL FOR
      SELECT * FROM Sectors WHERE sectorparent=@Sectorid

      OPEN RetrieveSectors

      FETCH NEXT FROM RetrieveSectors
      INTO @Sector_ID, @Sector_Name, @Sector_ParentID

      WHILE (@@FETCH_STATUS = 0)
      BEGIN
            INSERT INTO @retFindSectors
            SELECT * FROM dbo.GetSectors(0,@Sector_ID)
 
            INSERT INTO @retFindSectors
            VALUES(@Sector_ID,@Sector_Name, @Sector_ParentID)
   
            FETCH NEXT FROM RetrieveSectors
            INTO @Sector_ID, @Sector_Name, @Sector_ParentID
      END
      
      CLOSE RetrieveSectors
      DEALLOCATE RetrieveSectors

      RETURN
END

still not sure how this will let me group by each level ?? (sorry for my stupidity if this is not the case !!)

Thanks

JT

2 things:
*  DON'T USE Select *. I guess that your table SECTORS has more than those 3 columns...
* a correction to the suggestion given, the 2 inserts in the loop are actually wrong order...

here my test script (please test on a test database):

create table SECTORS (
sectorid SMALLINT Primary key,
sector  VARCHAR(100),
sectorparent SMALLINT ,
x char(1) default '1' )

go
insert into Sectors (sectorid, sector, sectorparent) values ( 1, 'ROOT', null )
insert into Sectors (sectorid, sector, sectorparent) values ( 2, 'MAIN 1', 1)
insert into Sectors (sectorid, sector, sectorparent) values ( 3, 'MAIN 2', 1)
insert into Sectors (sectorid, sector, sectorparent) values ( 4, 'CHILD 1 (1)', 2)
insert into Sectors (sectorid, sector, sectorparent) values ( 5, 'CHILD 2 (1)', 2)
insert into Sectors (sectorid, sector, sectorparent) values ( 6, 'CHILD 1 (2)', 3)

go

CREATE FUNCTION dbo.GetSectors(@includeparent bit, @sectorid int)
RETURNS @retFindSectors TABLE (sectorid int, sector varchar(100), sectorparent int)
AS  
BEGIN
     IF (@includeparent=1)
     BEGIN
          INSERT INTO @retFindSectors
          SELECT sectorid, sector, sectorparent FROM Sectors WHERE SectorID=@sectorid
      END

     DECLARE @Sector_ID int, @Sector_Name varchar(100), @Sector_ParentID int

     DECLARE RetrieveSectors CURSOR STATIC LOCAL FOR
     SELECT sectorid, sector, sectorparent  FROM Sectors WHERE sectorparent=@Sectorid

     OPEN RetrieveSectors

     FETCH NEXT FROM RetrieveSectors
     INTO @Sector_ID, @Sector_Name, @Sector_ParentID

     WHILE (@@FETCH_STATUS = 0)
     BEGIN
          INSERT INTO @retFindSectors
          VALUES(@Sector_ID,@Sector_Name, @Sector_ParentID)

          INSERT INTO @retFindSectors
          SELECT sectorid, sector, sectorparent  FROM dbo.GetSectors(0,@Sector_ID)
   
          FETCH NEXT FROM RetrieveSectors
          INTO @Sector_ID, @Sector_Name, @Sector_ParentID
     END
     
     CLOSE RetrieveSectors
     DEALLOCATE RetrieveSectors

     RETURN
END

go

select * from dbo.GetSectors(1, 1 )

go

drop function dbo.GetSectors
go
drop table SECTORS


output:

sectorid      sector      sectorparent
1      ROOT      
2      MAIN 1      1
4      CHILD 1 (1)      2
5      CHILD 2 (1)      2
3      MAIN 2      1
6      CHILD 1 (2)      3

if you have to have this "grouping" (ie indendation), here a changed version:

set nocount on
create table SECTORS (
sectorid SMALLINT Primary key,
sector  VARCHAR(100),
sectorparent SMALLINT ,
x char(1) default '1' )

go
insert into Sectors (sectorid, sector, sectorparent) values ( 1, 'ROOT', null )
insert into Sectors (sectorid, sector, sectorparent) values ( 2, 'MAIN 1', 1)
insert into Sectors (sectorid, sector, sectorparent) values ( 3, 'MAIN 2', 1)
insert into Sectors (sectorid, sector, sectorparent) values ( 4, 'CHILD 1 (1)', 2)
insert into Sectors (sectorid, sector, sectorparent) values ( 5, 'CHILD 2 (1)', 2)
insert into Sectors (sectorid, sector, sectorparent) values ( 6, 'CHILD 1 (2)', 3)

go

CREATE FUNCTION dbo.GetSectors(@includeparent bit, @sectorid int, @level int )
RETURNS @retFindSectors TABLE (sectorid int, sector varchar(100), sectorparent int, level int)
AS  
BEGIN
     IF (@includeparent=1)
     BEGIN
          INSERT INTO @retFindSectors
          SELECT sectorid, sector, sectorparent, @level-1 FROM Sectors WHERE SectorID=@sectorid
      END

     DECLARE @Sector_ID int, @Sector_Name varchar(100), @Sector_ParentID int

     DECLARE RetrieveSectors CURSOR STATIC LOCAL FOR
     SELECT sectorid, sector, sectorparent  FROM Sectors WHERE sectorparent=@Sectorid

     OPEN RetrieveSectors

     FETCH NEXT FROM RetrieveSectors
     INTO @Sector_ID, @Sector_Name, @Sector_ParentID
 
     SET @level = @level + 1

     WHILE (@@FETCH_STATUS = 0)
     BEGIN
          INSERT INTO @retFindSectors
          VALUES(@Sector_ID,@Sector_Name, @Sector_ParentID, @level-1 )

          INSERT INTO @retFindSectors
          SELECT sectorid, sector, sectorparent, level  FROM dbo.GetSectors(0,@Sector_ID, @level)
   
          FETCH NEXT FROM RetrieveSectors
          INTO @Sector_ID, @Sector_Name, @Sector_ParentID
     END
     
     CLOSE RetrieveSectors
     DEALLOCATE RetrieveSectors

     RETURN
END

go

select sectorid, sectorparent, level, replicate( ' ', level) + sector from dbo.GetSectors(1, 1 , 1 )

go

drop function dbo.GetSectors
go
drop table SECTORS

angelIII performance issues with this method compared to one to many tables for each level of sectors using inner joins??

Is there a big hit ?

I will be using the sectors and locations and associating them with three other table types and using these table for searching on..

eg bring back all users who are in the uk and have IT as their sector etc...

eg..
table1 sectors
table1idfk
sectoridfk

table1 locations
table1idfk
locationidfk


Cheers

JT

how long does the function take to return the full structure (for UK) you have?
that output will then be joined to the users table, and should run fast.

angellIII thanks for the function ......

I am getting myself confused about this ordering, i think it will be impossible to do with the function though, i want to display each record and their children under each other, and their childrens children etc..

eg

Parent
         Child1
               Child2
                     Child3
         Child
             Child1
etc...

the @level in the function only show how many levels it is down from the parent.........

JT

actually, the function should do what you want already... at least it did for me.

anyhow, let me suggest this variant:


CREATE FUNCTION dbo.GetSectors(@includeparent bit, @sectorid int, @level int )
RETURNS @retFindSectors TABLE (row int identity(1,1), sectorid int, sector varchar(100), sectorparent int, level int)
AS  
BEGIN
     IF (@includeparent=1)
     BEGIN
          INSERT INTO @retFindSectors (sectorid, sector, sectorparent, level)
          SELECT sectorid, sector, sectorparent, @level-1 FROM Sectors WHERE SectorID=@sectorid
      END

     DECLARE @Sector_ID int, @Sector_Name varchar(100), @Sector_ParentID int

     DECLARE RetrieveSectors CURSOR STATIC LOCAL FOR
     SELECT sectorid, sector, sectorparent  FROM Sectors WHERE sectorparent=@Sectorid

     OPEN RetrieveSectors

     FETCH NEXT FROM RetrieveSectors
     INTO @Sector_ID, @Sector_Name, @Sector_ParentID
 
     SET @level = @level + 1

     WHILE (@@FETCH_STATUS = 0)
     BEGIN
          INSERT INTO @retFindSectors (sectorid, sector, sectorparent, level)
          VALUES(@Sector_ID,@Sector_Name, @Sector_ParentID, @level-1 )

          INSERT INTO @retFindSectors (sectorid, sector, sectorparent, level)
          SELECT sectorid, sector, sectorparent, level  FROM dbo.GetSectors(0,@Sector_ID, @level)
   
          FETCH NEXT FROM RetrieveSectors
          INTO @Sector_ID, @Sector_Name, @Sector_ParentID
     END
     
     CLOSE RetrieveSectors
     DEALLOCATE RetrieveSectors

     RETURN
END

"actually, the function should do what you want already... at least it did for me"

do you have an example angelIII in html how you did it ??

Thanks

JT

This is my returned resultset from the function: -

LEVEL    SECTOR                        SECTORID      SECTORPARENT
0           IT                                        1                                 0
1           Programming                        3                                 1
1           Telecoms                             5                                 1
2           PHP                                6                                 3
2           CFM                                     7                                 3

And i want to output like this to HTML using (coldfusionMX)

IT (Level 0 SectorID 1 Sectorparent 0)
            Programming (Level 1 SectorID 3 Sectorparent 1)
                          PHP (Level 2 SectorID 6 Sectorparent 3)    
                          CFM(Level 2 SectorID 7 Sectorparent 3)
            Telecoms (Level 1 SectorID 5 Sectorparent 1)

Cheers

JT
                         

>do you have an example angelIII in html how you did it ??
you didn't mention html before, did you?
I only solved the SQL part...

anyhow, do you remember this call:
select sectorid, sectorparent, level, replicate( ' ', level) + sector from dbo.GetSectors(1, 1 , 1 )
you might try this to visualize it in the html output:
replicate('* ', level) + sector

angelIII i didnt have my coffee on friday lol.........

I realised when i read over the question again that the function done the ordering for me! all i had to do was tab it out with the #repeatstring(" ",getSector.level)# (coldfusion equilavent to replicate).

i added a little "order by sector ASC" to the function when it retrieves the sector details to be looped over, so the end result is the sectors being displayed by level and sector name Ascending, works well thanks angelIII........

One last question relating to this if i may.........

Is it possible to reverse this function ?, eg instead of searching for child sectors it will search for the parent sectors ??

The reason i ask this if a sector is selected and added to a particular user for example "Coldfusion" this might not be meaningful to a user... i think it would be more helpful to display its parents path before it eg...

IT - Programming - Web Design - Coldfusion

Thanks for your help

JT

lol..........

Spot On angelIII, you can leave out the "row int identity(1,1), " as i am just using the @level for ordering...

Getting a little nervous about performance using the adjacency list model as a user could have numerous sectors associated with them, so i would have to loop over each users sector to get each sectors individual parent path ??

Thanks again angelIII

JT

Hello angelIII any joy with the function ??

be doing a bit of reading on adjacency list model vs nested set and it looks like nested model is a little more efficient but not very good for situations where the data will change frequently (which is my case) would i be right in saying this ??

anyway found a bit of code for finding "The path to the root" using adjacency list model but it is for a fixed n level...

select node.name as node_name
     , up1.name as up1_name
     , up2.name as up2_name
     , up3.name as up3_name
  from categories as node
left outer
  join categories as up1
    on up1.id = node.parentid  
left outer
  join categories as up2
    on up2.id = up1.parentid  
left outer
  join categories as up3
    on up3.id = up2.parentid
order
    by node_name    

Thanks again

JT

Sorry angelIII again for pestering, have you had a stab at the parent path function ??

One last attempt.........

angelIII, have you had a stab at the parent path function ??

Thanks

JT