August 25, 2005 at 9:37 am
Hi guys
can any one point me to the right direction in writing the stored procedure to get the intersection of records..
Here is the data in my table
Resourceclasses
-------------------
ResourceClassId Name Type
1 Resource Classes 6
2 All Resources 5
4 List of Resource 5
5 Union 6
8 Union1 6
7 Intersection 7
3 Set1 5
6 Intersect 7
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
7 3 0
7 6 0
7 4 0
8 3 0
8 7 0
6 5 0
7 5 0
I wanted to get the resources for each Resource class based on the Type of Resource class
I am really stuck how to write the stored procedure.
Any help is greatly appreciated.
Thank you
August 25, 2005 at 9:43 am
Can you post the expected output??
August 25, 2005 at 9:49 am
Thanks Remi Gregoire
I am expecting the output in the follwing format
ResourceClassId ResourceId
3 1011
3 1001
3 1015
5 1011
5 1001
5 1015
5 1009
5 1004
6 1001
6 1009
6 1004
August 25, 2005 at 9:56 am
I don't understand the logic of this query.. can you dummy it up a little more?
August 25, 2005 at 10:21 am
i am sorry i haven't explained the problem clearly
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
-----------------------------
8 1011
8 1001
8 1015
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
simillarly it is same with ResourceClassId 8(of type Intersection)is made up of two classes 3 and 7 again This SubclassId 7 is made up of 4 subclasses
Hope i was able to explain my problem clearly..Apologise if any thing wrong with my explanation
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply