November 16, 2004 at 8:09 am
Hi All,
I have this problem with related items and updating a pair of tables.
I have 2 tables,
table 1 - Color
ID Description
0 None
1 Red
2 Blue
3 Green
4 Black
5 White
6 Bright Red
7 Rose
Table 2 - Matching Color
ColorID MatchingColorID
1 6
1 7
in order to keep the table data as small as possible I have omitted lots of the data. But looking at the MatchingColor table, it can be seen that Red (ID 1 from table Color) matches with Bright Red and Rose. What I need to be able to do is add matching colors which will essentially match all relevent matches to everything else. So in this example Red matches with Brigh Red and Rose, but Rose Does not with Red or Bright Red, and Bright Red does not match with Red or Rose. I need some way to be able to add a color and align all matches in every direction, so by saying Red Macthes with Rose and Bright red, I need to be able to associate Bright red with Rose, Rose with Bright Red, Bright Red with Red and Rose with Red.
The subject is headed Recursion and Cursors - I assume these are approaches I might try, but I have not got a clue where to start
Can anybody help me?
Thanks
November 16, 2004 at 12:19 pm
Hi,
First, I am color blind so the whole thing is lost on me. lol
Second, you will have circular references -- (i.e. red matches Orange and Orange Matches Red)
Third, Your data will be Garbage if you recurse too many levels--
(Red matches Orange
Orange Matches Yellow
Yellow Matches Green )
therefore Red matches Green
Fourth, This recursion will be a Recorce hog on your system
If you still want to proceed with this tack
Here is a function that Will recurse through all of the colors for you--
Create Function GetMatchingColors(@pk_color int)
Returns @temp Table (pk_Color int)
as
Begin
Insert into @temp (pk_Group)
select distinct MatchingColorId
FROM [Matching Color]
WHERE ColorID = @pk_color
Declare @Color int
declare Scroller cursor for
select distinct pk_Color
FROM @temp
OPEN SCROLLER
FETCH NEXT FROM SCROLLER INTO @Color
WHILE @@FETCH_STATUS=0
BEGIN
Insert into @temp (pk_Color)
select pk_Color FROM dbo.fn_GroupGroupsTable(@Color)
where pk_Color not in (select pk_Color from @temp)
FETCH NEXT FROM SCROLLER INTO @Color
END
close scroller
deallocate scroller
Return
end
Now When You Save this you will get an error because it is refering to itself and it is not built yet. Ignore that it will still build it.
HTH
Tal McMahon
November 16, 2004 at 8:08 pm
Thank you so much. I am not yet able to try out your answer. I'm sure it will work - I have taken on board your analysis of my problem and may change the schema at a later date to make it better. I am actually in the UK, I have just woken up and decided to have a mess on the web and saw your answer. I'll try it as soon as I get to work in a few hours.
Many thanks
Jim Bob.
November 17, 2004 at 12:43 am
Hi Jim,
I think that the solution to your problem is right in front of you. The structure that you already have can be used to model the necessary associations, but a change in the treatment of the relations is needed. Can't we just treat the ColorID 1 (Red) as a group of associated colors ? That way, using self join of Matching_Colors table you can obtain all colors that are associated with red, and that will mean that they belong to the same group. If you follow the inter-color relations down that table, you may end up chasing your tail (due to circular references) or exceeding maximum recursion level in SQL Server. I think that recursion is not the best choice here, you need to flatten somehow those color matching relationships. Of course, the limitation of my proposal is that you have to map each color to it's base group color. If you want to model some relationship like in Tal's example, you will have to associate one base (group) color to another.
You didn't describe in which for you want to query matching color: do you need to obtain a list of all colors matching to a specified color or a list of all colors that match to each other, or something else ?
Regards,
Goce.
November 17, 2004 at 6:26 am
Sorry Goce,
I will tell you exactly what I am doing which determines my need so you are more clear.
I am using ASP.NET (C#), and a web page is needed to display colors. I need to be able to add colors and match a color to 0 or many other colors, I also need to select a color from a dropdown list and show its matching colors along with the other colors that do not match.
In order to do this I have a dropdown list (DDL) containing all the colors from the Color table. Once I select a color from the DDL a listbox (LBMatching) will be populated with all matching colors and another listbox (LBNMatching) will be populated with those that are left. There will be 2 buttons between these list boxes one that will be used to push a color from LBNMatching to LBMatching and another to remove a color from LBMatching and put it in LBNMatching. after this is done I press a final button to update the database.
So in my example the DDL will have colors which are None, Red, Green, Blue, Yellow, Black, Pink, Bright Red, Rose, White. I chose red from DDL and there are currently no matches so LBNMatches will have all colors (apart from Red) and LBMatching will have no colors in it. If I then select Bright Red and Rose from LBNMatching and click the update button we then have an association with Red which are Bright Red and Rose.
By virtue of this action we should be able to chose Bright Red from the DDL and expect Red and Rose to appear in LBMatching, but this is not the case because I have not explicitly matched Bright Red to Red. The same should also happen if I choose Rose from the DDL.
The only way I can think in terms of relationships is that I have a Color table and a MatchingColor table, the color table has PK as ID and Description as the name of the color. In order to link these I have a MatchingColor table which has a ColorID field (as in the ID in Color table) and a MatchingColorID field as in the color that matches that of ColorID.
Because I have in the MatchingColor table an ID of 1 with a matching ID of 6 (Red and Bright Red) and another one with an ID of 7 (Red and Rose). I need to be able to map back to these and show that Rose matches with Bright Red and Red, Bright Red matches with Rose and Red.
Unfortunately the recursive function did not work so I am still in need of a solution,
any ideas would be very much appreciated
Jim bob
November 17, 2004 at 7:55 am
Hi Tal,
Thanks for the reply - unfortunately it did not work. I am pretty new to developing stored procedures hence the newbie question. there are a few things I need to ask you in terms of the function you gave me :
1. Where are we getting pk_Group from - which is the argument to the insert @temp statement?
2. how is dbo.fn_GroupGroupsTable(@Color) used and where is it defined?
3. How do I go about using this function from a stored proc? I know how to use functions that return say an int or a varchar, but have never done it based on returning a table. Do I need to declare a table in my proc and assign it to the return value of the function - not sure on this one
Can you help?
Thanks,
Jim Bob
November 17, 2004 at 11:07 am
Hi Jim,
I developped a similar UDF as Tal, but it does not use a recursion. I need to do some additional testing and I will post it tomorrow. It assumes several new things about the input data, but I think it will not be a problem to put them as table constraints.
Regards,
Goce.
November 17, 2004 at 11:55 am
Thank you so much Goce,
Jim Bob
November 18, 2004 at 2:23 am
Hi Jim,
after reading your detailed explanation, I realized that all colors should be treated equally, without taking one colors as "base" ones and others as attached to them. Ok, let's treat them so. My first proposal is to treat the matching colors table as bi-directional relation: if there is a record (color1, color2), this should mean that color2 is also matching to color1. But, to avoid situations like circular references, I put additional constraint on that table, allong with the 2 FK's to Colors table (I assume you already declared them). So, here is the structure of MatchingColors table I used for test:
create table MatchingColors (
ColorID int not null,
MatchingColorID int not null,
constraint pk_MatchingColors primary key (ColorID, MatchingColorID),
constraint fk_MatchingColors_Colors1 foreign key (ColorID) references Colors ([ID]),
constraint fk_MatchingColors_Colors2 foreign key (MatchingColorID) references Colors ([ID]),
constraint chk_MatchingColors_Ordered check (ColorID < MatchingColorID)
)
The new constraint requires that rows (color1, color2) always
have color1 < color2. Let's imagine that the table MatchingColors can be represented as a directed graph of matching. This constraint ensures that the graph can be followed in only one direction and matching color chain will have at most N = count(*) from Colors hops. This is the core idea of the solution, that uses neither recursion nor cursors:
create function dbo.udf_GetMatchingColors(@color_id int)
returns @res_tbl table (color_id int not null)
as
begin
declare
@num_colors int, -- Total number of colors (maximum matching path length)
@i int, -- Loop counter
@total_rows int, -- Current number of rows in the resulting table
@rows_inserted int -- Number of rows added in the current iterration
-- Seed the result table
insert into @res_tbl (color_id) values (@color_id)
set @rows_inserted = 1
set @total_rows = 1
select @num_colors = COUNT(*) from Colors
set @i = 1
while (@i 0))
begin
insert into @res_tbl
select
distinct color -- Take only distinct colors
from
(-- Add MatchingColorIDs that match ColorIDs in @res_tbl
select m_c.MatchingColorID as color
from @res_tbl as r
inner join MatchingColors as m_c on (m_c.ColorID = r.color_id)
union
-- Add ColorIDs that match MatchingColorIDs in @res_tbl
select m_c.ColorID as color
from @res_tbl as r
inner join MatchingColors as m_c on (m_c.MatchingColorID = r.color_id)
) as tmp
where
-- Do not add colors that already exists in @res_tbl
(not exists (select color_id from @res_tbl as r where (r.color_id = tmp.color)))
-- Increase the loop counter and update the number of newly added rows in this cycle.
-- This is used to decide whether it is necessary to loop through the maximum matching path
-- or enough search has been done.
set @i = @i + 1
select @rows_inserted = count(*) - @total_rows from @res_tbl
-- Now, update the total row counter
select @total_rows = count(*) from @res_tbl
end
-- Remove the input color from the result table
delete from @res_tbl where (color_id = @color_id)
return
end
go
Also, to ensure optimal performance of on of the matching subqueries, I added one new index:
create index ix_MatchingColors_MatchingColorID on MatchingColors (MatchingColorID)
The function is called, for example, like this:
select * from dbo.udf_GetMatchingColors(1)
and you can also use it in SELECTs as table or view.
Now, you can test he solution (make sure that records in MatchingColors table comply with the constraint) and see the results.
Regards,
Goce.
November 18, 2004 at 4:10 am
Hi Jim,
Now I see that the DISTINCT is obsolete in the SELECT, since the UNION will return a result set without the duplicate colors.
Regards,
Goce.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply