Help writing the stored procedure to get the following output? 1

[rrchowdhary]

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

 

[vongrunt]

Some questions... (RM = ResourceclassMembers, RC = Resourceclasses)

RM.ResourceclassID 4 is also of type (Set). So why this:

ResourceClassId ResourceId
4 1001
4 1009
4 1004

is not present in desired output set?

The way I see it, here are actions for RM.ResourceclassID 7:

7 (intersection) - 3 (set )
                 - 6 (intersection) - 4 (set )
                                    - 5 (union) - 4 (set)
                                                - 3 (set)
                 - 4 (set)
                 - 5 (union) - 4 (set)
                             - 3 (set)

In general, this makes tree structure in which all leaf nodes are of type (Set). Is that correct?

If true, then general idea is to make fundamental "operators" first (Set, Union, Intersection), then iteratively "scan" all trees from root outwards - with a little help of two temp tables: one for state maintenance (which nodes are already processes) and one for holding intermediate/final results. Is that what you want?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rrchowdhary]

Thanks vongrunt,

That is exactly what i was looking for.What i want the output to contain is the

intersection of Resourceclasses 3,6,4,5 so the final output should contain Just one Member which is 1001

so the outcome is

7 - 1001

when i look at the picture you drawn it looks easy to me but i have no clue how to write SP to do that task

 

[vongrunt]

To clarify:

Rows for RM.ResourceclassID = 4 should have values 1001, 1009 and 1004 ?

And for 8 - {1011, 1001, 1015} UNION {1001} = {1011, 1001, 1015} ?

Interesting problem... I'll try to solve it later today (when bloody phones stop ringing :E).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rrchowdhary]

Thanks vongrunt

I am out of my mind.you are right they should be in the final output too.
The reason i have omitted them in the desired output is to make the output simpler to go through.

 

[vongrunt]

Uff... intersection was tough... Ithinkididit:

-- create & fill results table with entries of type Set (5)
select RM.ResourceclassID, RM.ResourceID
into #results
from ResourceclassMembers RM
inner join Resourceclasses RC on RM.ResourceclassID = RC.ResourceclassID
where RC.Type = 5

--- create table state, fill it with already found classes:
select distinct R.ResourceClassID as RCID, convert(bit, 1) as processed
into #state
from #results R

-- loop until there is nothing more to do
while @@ROWCOUNT > 0
begin
	insert into #state (RCID, processed)
	select RM.ResourceClassID, 0 -- insert classes...
	from ResourceclassMembers RM
	left outer join #state S on RM.ResourceClassID=S.RCID
	left outer join  #state S2 on RM.SubclassID = S2.RCID
	where S.RCID is null -- ... that aren't previously inserted
	group by RM.ResourceClassID 
	having count(*) = count(S2.RCID) -- and completely depend on previously processed classes

	-- "Union" (6) - de-duped list of resources found in member classes
	insert into #results (ResourceclassID, ResourceID)
	select distinct RM.ResourceclassID, R.ResourceID
	from ResourceclassMembers RM
	inner join Resourceclasses RC on RM.ResourceclassID = RC.ResourceclassID
	inner join #results R on RM.SubclassID = R.ResourceclassID
	where RC.Type =6
		and RM.ResourceclassID in (select RCID from #state where processed=0)

	-- "Intersection" (7) - ResourceID must be present in all member classes
	insert into #results (ResourceclassID, ResourceID)
	select RM.ResourceclassID, R.ResourceID
	from ResourceClassMembers RM
	inner join Resourceclasses RC on RM.ResourceclassID = RC.ResourceclassID
	inner join #results R on RM.SubclassID = R.ResourceclassID
	where RC.Type = 7
		and RM.ResourceclassID in (select RCID from #state where processed=0)
	group by RM.ResourceclassID, R.ResourceID
	having count(*) = 
	(	select count(*) 
		from ResourceClassMembers RM2 
		where RM.ResourceclassID=RM2.ResourceclassID
	)

	-- mark resource classes as processed
	update #state
	set processed = 1 where processed = 0
end

select * from #results order by ResourceclassID, ResourceID

drop table #state
drop table #results

There will be surely a lot of questions anyway, so here is general idea only:

Processing is done in "layers" according to class dependency. One layer = one WHILE loop. Each cycle processes resource classes that depend entirely on previously processed classes. Looping continues until there is nothing else to process. What is processed so far is stored in table #state, while table #results keeps intermediate and final results.

Total number of iterations equals max. depth of tree. Compared to cursors & blah this should fly.

If for some reason live data does not always contain sets (5) in leaf tree nodes (some kind of "shortcut" or "alias"?), this requires one additional trivial query within a WHILE loop.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rrchowdhary]

Hi vongrunt

Thanks very much for this query.I have just returned to the work and run the query againest my test data and it just rocks.

I just felt like you saved me from the sinking ship when i have seen your solution.
still i don't know how you approached the problem but certainly i thought without using many cursors it won't be possible to get the solution. but you proved me i am wrong.

I have gone through the quey you have written ,but i think it takes some time for me to understand the query.I need to improve my skills to understand the logic you have used in writing the query.

Mean while i just wanted to know how we can get the grips to writing such queries.I am writing
sql queries for months but get stuck whenever if i wanted to write any complicated query

Can you also help me in finding the Resources for the ResourceClass Type Difference(8).I thought i could do this myself ,once we get the solution for union and Intersection buti need your help with this too

Can you also just point me where do i need to change in the query if i just wanted to find Resources by just passing the one ResourceclassId


The data for DifferenceClass Type(8) in my table is like this

ResourceclassId	Type	DifferenceA DifferenceB
9		  8	         4		7
10		  8	         4		9
11		  8	         4		3

This DifferenceA and DifferenceB are ResourceclassId's in the same Table


Thanks once again

 

[vongrunt]

> Mean while i just wanted to know how we can get the grips to writing such queries.

I never thought about that...

Instead of going procedural - aka: row-by-row processing - imagine how to solve problem with fewer set-based steps. This includes working with temp tables, mass INSERTs, UPDATEs and DELETEs. Joins, aggregated functions, NULLs and derived tables are fundamental. It takes some time but after some point you begin to "think SQL", discover various tricks and wonder about suddenly obvious things. After that sky is the limit.

> Can you also help me in finding the Resources for the ResourceClass Type Difference(8).

There are two sets: {1, 2} and {2, 3}. What is Type Difference supposed to return? {1}, {3} or {1, 3}?

And... DiffA/DiffB columns, do they belong to ResourceclassMembers table or somewhere else? Can you post complete sample data (all tables)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rrchowdhary]

Thanks vongrunt

I will try to learn writing sql queries this way..i need to shed more light in writing these
type of queries..


Difference type should return Difference between two classes..


ResourceClasses table contains two additional columns which used to hold
classid's for Differenceclass Type(8)

as in our sample data table i have taken of those two columns(DifferenceA,DifferenceB) in the ResourceClasses table

so the original table structure of Resourceclasses is

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

9 Difference1 8 4 7
10 Difference2 8 4 9
11 Difference3 8 4 3



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


the output for this Differenceclass should contain

9 1009
9 1004

10 1001

11 1009
11 1004

 

[rrchowdhary]

Type Difference {1, 2} and {2, 3} should return nothing

as the Definition says We should get the Resources which belongs to ClassA but does not belong to classB

and also DiffA and DiffB columns belongs to Resourceclass table

 

[vongrunt]

> as the Definition says We should get the Resources which belongs to ClassA but does not belong to classB

ClassA = {1, 2}
ClassB = {2, 3}

1 belongs to classA but not to classB, right?



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rrchowdhary]

umm..unless i get this solution right i can't put my mind to what i am typing.

that's right..it's my mistake

 

[rrchowdhary]

Hi vongrunt,

I did not quite get the first statement in the while loop(in your query).Can you please give me an idea What exactly it is holding the information.I think the logic lies in that statement.

and also i have requested in one of the reply to, is there any way we could modify the query to accept the ResourceclassId as a input parameter and return resourceId belongs to that resourceclassId

where exactly your Query looping through each resourceclass

Thanks

 

[vongrunt]

You probably think about this:

insert into #state (RCID, processed)
select RM.ResourceClassID, 0
from ResourceclassMembers RM
left outer join #state S on RM.ResourceClassID=S.RCID
left outer join  #state S2 on RM.SubclassID = S2.RCID
where S.RCID is null
group by RM.ResourceClassID 
having count(*) = count(S2.RCID)

ResourceclassMembers (RM) table is the source.

Table #state holds already processed classes so far - one row per class. It is instanced twice (S and S2 aliases). With sample data you gave, during first WHILE loop entry it will contain two rows with classes of type Set - 3 and 4.

S is used to determine classes that aren't processed ( LEFT JOIN trick, see where S.RCID is null line ). So every WHILE cycle will work only with "newer" classes,

S2 us used to join on subclasses. For example, class 5 has subclasses 3 and 4. If class has subclasses that aren't processed so far, data from S2 will be filled with NULL values (this is natural LEFT JOIN behaviour).

GROUP BY eliminates duplicates

HAVING is kind of nifty... COUNT(*) also counts NULL values while COUNT(somecolumn) does not. So if COUNT(*) equals COUNT(S2.RCID) class completely depends on already processed subclasses.

Take a look at this:

7 (intersection) - 3 (set )
                 - 6 (intersection) - 4 (set )
                                    - 5 (union) - 4 (set)
                                                - 3 (set)
                 - 4 (set)
                 - 5 (union) - 4 (set)
                             - 3 (set)

Code before WHILE loop will insert rows with RCID=3 and RCID=4 and mark them as "processed".
First-pass INSERT will find&insert value 5, because it depends on 3 and 4.
Second-pass INSERT will find value 6 (intersection of 5 and 4).
Etc etc.

Again: this table holds only "markers" about what is processed so far. All results are stored in table #results.


> and also i have requested in one of the reply to, is there any way we could modify the query to accept the ResourceclassId as a input parameter and return resourceId belongs to that resourceclassId

Let's do TypeDiff "operator" first....

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

If that's still confusing, try stripped-down version of code below.

Sample data (for anyone interested):

create table Resourceclasses
(	ResourceclassId int,
	Name varchar(20),
	Type tinyint,
	DifferenceA int,
	DifferenceB int
)

insert into Resourceclasses values (1, 'Resource Classes', 6, 0, 0)
insert into Resourceclasses values (2, 'All Resources', 5, 0, 0)
insert into Resourceclasses values (3, 'Set1', 5, 0, 0)
insert into Resourceclasses values (4, 'List of Resource', 5, 0, 0)
insert into Resourceclasses values (5, 'Union', 6, 0, 0)
insert into Resourceclasses values (6, 'Intersect', 7, 0, 0)
insert into Resourceclasses values (7, 'Intersection', 7, 0, 0)
insert into Resourceclasses values (8, 'Union1', 6, 0, 0)
-- insert into Resourceclasses values (9, 'Difference1', 8, 4, 7)
-- insert into Resourceclasses values (10, 'Difference2', 8, 4, 9)
-- insert into Resourceclasses values (11, 'Difference3', 8, 4, 3)

create table ResourceclassMembers
(	ResourceClassId int,
	SubclassId int,
	ResourceId int
)

insert into ResourceclassMembers values (3, 0, 1011)
insert into ResourceclassMembers values (3, 0, 1001)
insert into ResourceclassMembers values (3, 0, 1015)
insert into ResourceclassMembers values (4, 0, 1001)
insert into ResourceclassMembers values (4, 0, 1009)
insert into ResourceclassMembers values (4, 0, 1004)
insert into ResourceclassMembers values (5, 4, 0   )
insert into ResourceclassMembers values (5, 3, 0   )
insert into ResourceclassMembers values (6, 4, 0   )
insert into ResourceclassMembers values (6, 5, 0   )
insert into ResourceclassMembers values (7, 3, 0   )
insert into ResourceclassMembers values (7, 6, 0   )
insert into ResourceclassMembers values (7, 4, 0   )
insert into ResourceclassMembers values (7, 5, 0   )
insert into ResourceclassMembers values (8, 3, 0   )
insert into ResourceclassMembers values (8, 7, 0   )

Run this in Query Analyzer:

-- create & fill results table with entries of type Set (5)
select RM.ResourceclassID, RM.ResourceID
into #results
from ResourceclassMembers RM
inner join Resourceclasses RC on RM.ResourceclassID = RC.ResourceclassID
where RC.Type = 5

--- create table state, fill it with already found classes:
select distinct R.ResourceClassID as RCID, convert(bit, 1) as processed
into #state
from #results R

-- loop until there is nothing more to do
while @@ROWCOUNT > 0
begin
	insert into #state (RCID, processed)
	select RM.ResourceClassID, 0 -- insert classes...
	from ResourceclassMembers RM
	left outer join #state S on RM.ResourceClassID=S.RCID
	left outer join  #state S2 on RM.SubclassID = S2.RCID
	where S.RCID is null -- ... that aren't previously inserted
	group by RM.ResourceClassID 
	having count(*) = count(S2.RCID) -- and completely depend on previously processed classes

	select *
	from #state 
	where processed = 0

	-- mark resource classes as processed
	update #state
	set processed = 1 where processed = 0
end

drop table #state
drop table #results

... and see how processing goes on with each loop.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

About type difference: add code in bold:

...
	        where RM.ResourceclassID=RM2.ResourceclassID
    )

[b]-- insert Type Difference (8) classes
	insert into #state (RCID, processed)
	select  RC.ResourceclassID, 0
	from Resourceclasses RC
	left outer join #state S0 on RC.ResourceclassID=S0.RCID
	inner join #state S on RC.DifferenceA=S.RCID -- DiffA must be processed
	inner join #state S2 on RC.DifferenceB=S2.RCID -- DIffB too...
	where RC.Type = 8
		and S0.RCID is null -- not processed so far

	-- Type Difference (8) - ResourceID from subclass DiffB must exists in subclass DiffA
	insert into #results (ResourceclassID, ResourceID)
	select RC.ResourceClassId, R.ResourceId
	from Resourceclasses RC
	inner join #state S on RC.ResourceclassId=S.RCID
	inner join #results R on RC.DifferenceA=R.ResourceClassId
	left outer join #results R2 on RC.DifferenceB=R2.ResourceClassId and R.ResourceId=R2.ResourceId
	where RC.Type=8 and S.processed=0
		and R2.ResourceClassId is null[/b]

    -- mark resource classes as processed
    update #state
    set processed = 1 where processed = 0
end
...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rrchowdhary]

Thanks vongrunt

This just works great.

 

[vongrunt]

Now, the only thing that remains is request about getting resourceIDs for a single ResourceclassId.

If data changes rarely I'd suggest you to use permanent table (say, Results) instead of temporary table #results - and run code from above when necessary (after changes, once per day or something). With that logic list of resources is easy to pull out:

SELECT ResourceId
FROM Results
WHERE ResourceclassID= <specified ID>

If data changes frequently - and you query for a single resource class a lot - then code I provided is not efficient. It is supposed to calculate resources for all classes at once by using bottom-up iterative algorithm. In that case it would be better to build list of dependent classes for specified resource class first (something like #state table) and then process only these classes.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rrchowdhary]

Thanks vongrunt,

What i have done to get Resources for a perticular ResourceclassId is to add where class in the Final select statement.

select * from #results
where ResourceClassID=@ResourceclasId
order by ResourceclassID, ResourceID

you are right it is not that efficient to process all the Resourceclasses to get a single Resourceclass.But i will use this solution until i can find a work around using the solution you have given to filter records based on ResourceclassId

I can't use permanent table as data changes very frequently.

once again thanks very much for all the help you have given.