Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nkosinathi
Starting Member
4 Posts |
 Posted - 2013-05-10 : 09:11:32
|
| Good afternoon everybody, i am a graduate still new to sql programming. I have this query:I have been tasked to create an sql script which searches a tree structure that shows Connectivity of devices.The structure from high level is as follows: Station>incomer>feederboard>feeder>load centres. where there is one or more load centres.My problem is that the script i created it only shows one load centre. Anyone please assist me how to search through a feeder to display if there is one or more load centres there.I will upload the script if there is a need.nknkosi@smith |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-10 : 09:20:25
|
| If you post the code that you have with some sample data and table schema, that would make it clearer to people so they can offer useful suggestions.It sounds like you could make use of the hierarchyid data type available in SQL 2008. See this article: http://msdn.microsoft.com/en-us/magazine/cc794278.aspxThis data type is available specifically to model tree-like hierarchies such as the one you described. |
 |
|
|
Nkosinathi
Starting Member
4 Posts |
Posted - 2013-05-10 : 09:21:52
|
| Thats the code i did;select (select description from device_fr where id = feederboard_con.fromdevice_id ) as incomer,feeder_board.description FeederBoard,(select description from device_fr where id = feeder_con.todevice_id ) as feeder,(select description from device_fr where id = lc1_1_con.todevice_id ) as LC1from device_fr station left outer join device_fr feeder_board on (feeder_board.parentdevice_id = station.id)left outer join device_fr incomer on (incomer.parentdevice_id = feeder_board.id and incomer.devicetype_id in (23))left outer join deviceconnection feederboard_con on (feederboard_con.fromdevice_id = incomer.id)left outer join deviceconnection feeder_con on (feeder_con.fromdevice_id = feeder_board.id) left outer join deviceconnection lc1_con on ( lc1_con.fromdevice_id = feederboard_con.todevice_id )left outer join deviceconnection lc1_1_con on ( lc1_1_con.fromdevice_id in (select id from device_fr where id = feeder_con.todevice_id ) )where station.id = 8;This is the sample i have after compile:incomer feederboard feeder load cenre1transformer feederboard1 feeder1 df malan 425nknkosi@smith |
|
|
|
|
|
|
Looping a tree search