Help in writing the SP to get this output

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

SQL Server 2000 Forums

SQL Server Development (2000)

Help in writing the SP to get this output

Author

Topic

rcchowdhary
Starting Member

3 Posts

Posted - 2005-08-26 : 05:33:38

Hi

I have stucked up with writing stored procedure to get the desired output.would appreciate help in from you all

i would like to get the intersection,union and set of records based on the Type

Here is the data in my table

Resourceclasses
-------------------
ResourceClassId Name Type
-----------------------------------------------
1 Resource Classes 6
2 All Resources 5
3 Set1 5
4 List of Resource 5
5 Union 6
6 Intersect 7
7 Intersection 7
8 Union1 6

Type 5:Set of Resource classes
Type 6:Union of Resource classes
Type 7:Intersection of Resource classes

ResourceclassMembers
----------------------
ResourceClassId SubclassId ResourceId
-------------------------------------------
3 0 1011
3 0 1001
3 0 1015

4 0 1001
4 0 1009
4 0 1004

5 4 0
5 3 0

6 4 0
6 5 0

7 3 0
7 6 0
7 4 0
7 5 0

8 3 0
8 7 0

Desired Output
ResourceClassId ResourceId
3 1011
3 1001
3 1015
---------------------------
5 1011
5 1001
5 1015
5 1009
5 1004
-------------------------------
6 1001
6 1009
6 1004
-----------------------------

Each ResourceclassId in the Resourceclass Table belongs to a Type which is either(5-Set,6-Union,7-Intersection)

which is linked to ResourceClassmembers table

If you look at the ResourceClasses table

ResourceClassId 5 is made up of (Union of)two resourceclasses 3 and 4

so the output for ResourceclassId 5 is
5 1011
5 1001
5 1015
5 1009
5 1004

where as the ResourceclasId 3 is of type(Set)which has no Subclasses so we return ResourceId for them.

3 1011
3 1001
3 1015

simillarly it is same with ResourceClassId 6(of type Intersection)is made up of two classes
4 and 5

Hope i was able to explain my problem clearly..Apologise if any thing wrong with my explanation

Thanks

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-08-26 : 07:05:37

show us what u have tried and what is wrong with it

rcchowdhary
Starting Member

3 Posts

Posted - 2005-08-26 : 07:36:31

Thanks for the reply coolerbob

I am trying the solution using lot of temporary tables and cursors.It just looking so awkward solution and it is getting difficult to even debug the sp's

What is the best way to solve this problem

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 09:04:08

See if these are helpful
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources