Solved

Hierarchy  SQL Problem

Posted on 2014-11-07
10
96 Views
Last Modified: 2014-11-07
I have a self referencing table called Location

LocationID, ParentLocationID, Name

I can get a Hierarchy view with this query, that return data that will bind to a treeview.

With Hierarchy (Location_ID,ParentLocation_ID, Location)
AS
(
Select Location_ID,ParentLocation_ID, Location From Location
)

SELECT * FROM  Hierarchy


1      NULL      Main Wait
6      1      Reception Desk 1
7      1      Reception Desk 2
8      1      Reception Desk 3
9      1      Reception Desk 4
10      1      Reception Desk 5
11      1      Reception Desk 6
12      1      Reception Desk 7
13      1      Reception Desk 8
14      NULL      Seconday Location
15      14      Reception Area


I also have 2 more tables a Resources table containing things like Chairs, desks, Newtoek conections etc,

ResourceID, Name, Description


and a linking table tp give me a many to many link between Resource and Locations

ResourceID, LocationID.

What I want to do is include any resources in the main location hierarchy, so that when bound to a tree view will appear under the location. I intend to have a flag to say whether the node is a Location or a resource so OnRender I can style differently.

1      NULL      Main Wait      Type
6      1      Reception Desk 1      L
1      1      Desk      R
2      1      Phone      R
7      1      Reception Desk 2      L
8      1      Reception Desk 3      L
9      1      Reception Desk 4      L
10      1      Reception Desk 5      L
11      1      Reception Desk 6      L
12      1      Reception Desk 7      L
13      1      Reception Desk 8      L
14      NULL      Secondary Location      L
15      14      Reception Area      L
1      14      Desk      R
3      14      Chair      R

I'm struggling with the SQL.

Can anyone help please or suggest a better way to display locations and their Resources

Andy
0
Comment
Question by:Andy Green
  • 5
  • 4
10 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40427977
Could you provide some raw data please (instead of results) for tables:
 Location,
 the linking table, and the
 Resources table
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40427981
How do you get the ParentLocation_ID for your resources? For example, in the final result table I see in line 4 that the Phone has locationID 2, and ParentLocation_ID 1. But in your data for the locations locationID 2 does not exist.
I agree with Paul, some sample data that works out to a full output example would be helpful.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40427988
Location_ID      ParentLocation_ID      Location
1      NULL      Main Wait
6      1      Reception Desk 1
7      1      Reception Desk 2
8      1      Reception Desk 3
9      1      Reception Desk 4
10      1      Reception Desk 5
11      1      Reception Desk 6
12      1      Reception Desk 7
13      1      Reception Desk 8
14      NULL      Seconday Location
15      14      Reception Area



Resource_ID      Name      Description
1      Hot Desk      Desk / Chair / PC / Network connection
2      Desk      NULL
3      Chair      NULL
4      Network Connection      NULL
5      Office Thing      NULL
6      Phone       Null




Resource_ID      Location_ID
2      6
6      6
2      15
3      15

This doesn't provide the data shown in the OP. I have tried to piece it together so its valid.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40428050
The parent location will be the location ID, so they hang under the correct location.

Just looking at this and I don't think its possible as we have two Locations_IDs of the same value. (This is where the ResourceID will be aliased.) Could call the first COlumn ID as its not necessarily the location or Resource.

Andy
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40428055
Will this work?

create table #location
(	Location_ID int
,	ParentLocation_ID int
,	Name nvarchar(100));

create table #Resources
(	Resource_ID	int
,	Name		nvarchar(100)
,	Description	nvarchar(100));

Create table #links
(	Resource_ID	int
,	Location_ID	int
);

Insert into #location
values		(1, NULL, 'Main Wait')
		,	(6, 1, 'Reception Desk 1')
		,	(7, 1, 'Reception Desk 2')
		,	(8, 1, 'Reception Desk 3')
		,	(9, 1, 'Reception Desk 4')
		,	(10, 1, 'Reception Desk 5')
		,	(11, 1, 'Reception Desk 6')
		,	(12, 1, 'Reception Desk 7')
		,	(13, 1, 'Reception Desk 8')
		,	(14, NULL, 'Seconday Location')
		,	(15, 14, 'Reception Area');
		
insert into #Resources
values		(1, 'Hot Desk', 'Desk / Chair / PC / Network connection')
		,	(2, 'Desk', NULL)
		,	(3, 'Chair', NULL)
		,	(4, 'Network Connection', NULL)
		,	(5, 'Office Thing', NULL)
		,	(6, 'Phone', Null);
		
insert into #links
values		(2, 6)
		,	(6, 6)
		,	(2, 15)
		,	(3, 15);
		
With Hierarchy (Location_ID,ParentLocation_ID, Location, TypeFlag)
AS
(
Select		Location_ID
		,	ParentLocation_ID
		,	Name
		,	'L' 
From #location 

union all

select		lo.Location_ID
		,	lo.ParentLocation_ID
		,	r.Name
		,	'R'
from	#Resources r
		inner join #links l
			on r.Resource_ID = l.Resource_ID
		inner join #location lo
			on l.Location_ID = lo.Location_ID

)

select	*
from	Hierarchy
order by Location_ID


drop table #Resources
drop table #location
drop table #links

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40428067
Ok, based on your last comment you might then want to change the CTE to:

With Hierarchy (Location_ID,ParentLocation_ID, Location, TypeFlag)
AS
(
Select		Location_ID
		,	ParentLocation_ID
		,	Name
		,	'L' 
From #location 

union all

select		r.Resource_ID
		,	lo.ParentLocation_ID
		,	r.Name
		,	'R'
from	#Resources r
		inner join #links l
			on r.Resource_ID = l.Resource_ID
		inner join #location lo
			on l.Location_ID = lo.Location_ID

)

select	*
from	Hierarchy
order by ParentLocation_ID

Open in new window


But your first column is pretty meaningless by the looks of it.
I'm not quite clear why you're using a CTE in the first place though, as the location table seems to have the exact same structure already. Why don't you use a standard select statement for the records?

Select		Location_ID
		,	ParentLocation_ID
		,	Name
		,	'L' as 'TypeFlag'
From #location 

union all

select		r.Resource_ID
		,	lo.ParentLocation_ID
		,	r.Name
		,	'R'
from	#Resources r
		inner join #links l
			on r.Resource_ID = l.Resource_ID
		inner join #location lo
			on l.Location_ID = lo.Location_ID

Order by 2

Open in new window

0
 
LVL 3

Author Comment

by:Andy Green
ID: 40428077
Thanks Koen

I have run it and can see some issues.

Assuming you run it as is I'll reference the row numbers.

Rows 3&4 the parentiD should be 6
Row 14 & 15 Parent ID should be 15

Location ID should be:
row 3 - 2
row 4 - 6 (as it is )
row 14 - 2
row 15 - 3

Like I have said this may not bind as there will be multiple id in the first column, but that's my problem :-)

But looking good, thank you

Andy

r
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40428081
To change the ParentID's just change the select statement back to this:

Select		Location_ID
		,	ParentLocation_ID
		,	Name
		,	'L' as 'TypeFlag'
From #location 

union all

select		r.Resource_ID
		,	lo.Location_ID
		,	r.Name
		,	'R'
from	#Resources r
		inner join #links l
			on r.Resource_ID = l.Resource_ID
		inner join #location lo
			on l.Location_ID = lo.Location_ID

Order by 2

Open in new window


I'm not quite sure what you mean with your description of the location ID's (what it should be).
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40428088
Thanks, the first column is the location ID, but when unioned with the Resources table the ID will be the ID of the resource, so its not the Location ID, hence it make more sense to just call it ID.

The code I started with was from a proc without a natural structure, I just took it and made it work, not really considering the table structure was already in the right format.

Andy
0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 40428092
Thank you, just what I need.

Andy
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

19 Experts available now in Live!

Get 1:1 Help Now