I am currently writing a SQL query that should display a tree-view of areas inside a building with areas, sub-areas, etc. Unfortunately I have been unable to mimic the ordering used by some of our software tools. I'm restricted to MS SQL 2000 so the question of order becomes much more complicated and I'm just over my head at this point.
The ordering logic is that the Child column and Parent column are related. If the value of the 1st row's Child column matches the 2nd row's Parent column, then the 2nd row goes after the first.
--How it currently returns data
Child Level Parent
562 Campus 0
86 Area 1
87 Area 1
88 Area 1
90 Sub-Area 86
91 Sub-Area 86
92 Sub-Area 87
93 Sub-Area 87
94 Sub-Area 88
95 Sub-Area 88
3 Unit 90
16 Unit 90
4 Unit 91
6 Unit 91
etc, so on and therefore
--How I want it to return the data
Child Level Parent
562 Campus 0
1 Building 562
86 Area 1
90 Sub-Area 86
91 Sub-Area 86
87 Area 1
95 Sub-Area 87
95 Sub-Area 87
For this logic to work correctly it would need to do something like
- Return the building rows with their Parent and Child codes
- Match Area Parent codes to the building Child codes, then insert Area rows under the appropriate Building row.
- Match Sub-Area Parent codes to the Area Child codes, then insert Sub-Area rows under the appropriate Area.
- Match Unit Parent codes to the Sub-Area Child codes, then insert the Unit rows under the appropriate Sub-Area
If this actually possible with SQL?
I would love to know if it is as I'm hesitant to invest any more time into this unless I know it's actually a possibility. I realize that I could write a CASE statement with a custom mapping for an ORDER BY statement, but that won't work for any other campus (parent/child codes are different) and I would love to be able to re-use this code in the future with minimal customization.
Thanks!
EDIT: Adding Query as requested
DECLARE
@BuildingType int,
@CampusType int
SET @BuildingType= 4
SET @CampusType= 1
select
b.fkabc_building_child,
(select isnull(c.collectionname, 'none')
from abc_collections c
where c.pkabc_collections = b.fkabc_building_child) as 'Child Collection',
l.floorname,
isnull(b.fkabc_collections_parent,0) as fkabc_collections_parent,
b.fkabc_floorbreakdowns
from abc_breakdowns r
left join abc_floorbreakdowns fr
on fr.pkabc_floorbreakdowns = b.fkabc_floorbreakdowns
inner join abc_buildingtypescampustypes btct
on btct.pkabc_buildingtypescampustypes = fr.fkabc_buildingtypescampustypes
inner join abc_buildingtypes bt
on btct.fkabc_buildingtypes = bt.pkabc_buildingtypes
inner join abc_collectiontypes ct
on btct.fkabc_collectiontypes = ct.pkabc_collectiontypes
inner join abc_collections c
on b.fkabc_building_child = c.pkabc_collections
inner join abc_floors l
on l.pkabc_floors = c.fkabc_floors
where bt.pkabc_buildingtypes = @BuildingType
and ct.pkabc_collectiontypes = @CampusType