Solved

Ordering By Child Records

Posted on 2006-11-10
23
193 Views
Last Modified: 2010-03-15
Okay i have a location table in which i relate each location to a parent location.The top level locations dont have a parent location specified. There can be a number of different levels of locations and all i want to achieve is to output all child locations ordered by each level down ??

Do i need some type of union statement maybe ??

UK
   Scotland
         Highlands
         Glasgow
   England
         London
         Manchester
   N.Ireland
         Co.Antrim
               Belfast  
etc.............
 

LOCATIONS TABLE
locationid         INT PK
location           VARCHAR
locationparent  INT FK

Cheers

JT

0
Comment
Question by:jturkington
  • 12
  • 9
23 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17913199
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
0
 

Author Comment

by:jturkington
ID: 17913211
i have sql server 2000

JT
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17913223
0
 

Author Comment

by:jturkington
ID: 17913259
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17913288
Check out the code in Figure 5.
you have to put your tablename andfield names instead.
0
 

Author Comment

by:jturkington
ID: 17913413
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17913458
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

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17913484
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
0
 

Author Comment

by:jturkington
ID: 17913618
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17913677
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:jturkington
ID: 17914222
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17914694
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
0
 

Author Comment

by:jturkington
ID: 17914787
"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
0
 

Author Comment

by:jturkington
ID: 17915001
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
                         
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17915146
>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

0
 

Author Comment

by:jturkington
ID: 17948957
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
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17949048
>Is it possible to reverse this function ?, eg instead of searching for child sectors it will search for the parent sectors ??
yes, that is possible. but for that I have to get another cup of coffee :-)
so, you want for the items to get the full path up to the root, correct. in addition what it already returns right now?
0
 

Author Comment

by:jturkington
ID: 17949098
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
0
 

Author Comment

by:jturkington
ID: 17954391
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
0
 

Author Comment

by:jturkington
ID: 17979545
Sorry angelIII again for pestering, have you had a stab at the parent path function ??
0
 

Author Comment

by:jturkington
ID: 18131977
One last attempt.........

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

Thanks

JT
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now