• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 107
  • Last Modified:

Hierarchy SQL Problem

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
Andy Green
Asked:
Andy Green
  • 5
  • 4
1 Solution
 
PortletPaulCommented:
Could you provide some raw data please (instead of results) for tables:
 Location,
 the linking table, and the
 Resources table
0
 
Koen Van WielinkIT ConsultantCommented:
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
 
Andy GreenAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Andy GreenAuthor Commented:
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
 
Koen Van WielinkIT ConsultantCommented:
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
 
Koen Van WielinkIT ConsultantCommented:
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
 
Andy GreenAuthor Commented:
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
 
Koen Van WielinkIT ConsultantCommented:
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
 
Andy GreenAuthor Commented:
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
 
Andy GreenAuthor Commented:
Thank you, just what I need.

Andy
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now