help needed in this complex logic

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

help needed in this complex logic

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-03-28 : 13:22:56

Table:GRoupAllocation
______________________
GroupId (Primarykey)
GroupName

Table:SystemAllocation
_______________________
SystemId(Primarykey)
SystemName
GroupId (foreignkey)

Table:DeviceAllocation
_______________________
DeviceId (Primarykey)
DeviceName
SystemId (foreignkey)
_______________________

Table:SensorAllocation
_______________________
SensorId (Primarykey)
SensorName
GroupId (Primarykey)
SystemId (foreignkey)
_______________________

Table:GRoupAllocation
______________________
GroupId GroupName
1 Group1
2 Group2
3 GRoup3
4 Group4

Table:SystemAllocation
___________________________
SystemId SystemName GroupId
1 system1 1
2 system2 2
3 system3 3
4 system4 2
5 system5 1

Table:DeviceAllocation
___________________________
DeviceId DeviceName SystemId
1 Device1 1
2 Device3 3
3 Device4 2

Table:SensorAllocation
____________________________________________
SensorId SensorName GroupId SystemId DeviceId
1 sensor1 1 1 1
3 sensor3 2 2
4 sensor4 3 3 3

my results should be like this:

Results:
___________________________________________________________________
GroupName SystemName DeviceName SensorName
___________________________________________________________________
Group1 system1 Device1 sensor1
Group1 null null sensor2
GRoup2 system2 null sensor3
GRoup2 system4 null null
Group3 system3 Device3 sensor4
Group4 null null null

so i need to populate the results in treeview.my treeview looks lika this:

Group1
|____System1
|________Device1
|_______sensor1

|_______system5

Group2
|____System2
|________Device1
|_______sensor1

|____System4
|____Device4

like this i need to populate. i need to show all the groupname and belonging systemName and belonging devicename and belonging sensorname

so please give me query for this complex operation please
criteria's
1.GRoup can have systems and system can have devices and device can have sensors
2.GRoup can have systems and systems can have sensors[no device]
3.GRoup can have systems and systems can have devices [no sensor]
4.GRoup can only have system [no device, no sensor]
5.GRoup can have only sensor[no system, no device]
so please

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-03-28 : 13:25:37

sorry,
sensor allocation table structure is as follows:

Table:SensorAllocation
_______________________
SensorId (Primarykey)
SensorName
GroupId (foreignkey)
SystemId (foreignkey)
deviceId (foreignkey)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-28 : 13:33:06

Look for expanding hierarachies in below:-

http://msdn2.microsoft.com/en-us/library/aa172799(SQL.80).aspx

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-03-28 : 14:03:02

dear visakh16,

ithe link which u have given they used SPC. i need to use query for this.i sould not use stored procedure.s oplease give query for my need please

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2008-03-29 : 13:21:12

please can any one help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-29 : 15:41:57

You can use a recursive CTE of you are on a Microsft SQL Server 2005.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources