Hi,
I have to go through some tables, like a network. I have a recursive
algorithm, but I need to do it iteratively.
Basically, there are 2 tables through which I have to go: Items table and
Relationship table.
The tables I have defined are like this:
- Item table, 2 fields: ItemID, ItemWeight
- Relationships table, 3 fields: Item1, Item2, LinkWeight.
Given Item x, I need to get all of the items related to it. It has to go
down through that item's related items if relationship weight is greater tha
n
item weight.
It stops
- when relationship weight is lower than item weight,
- it has already propagated all of the items
I have this algorithm:
I defined 2 global variables:
- PropagatedItems - contains all of the items that have been "visited" and
their related items
- FoundItems: contains the items that were visited but not its related items
Sub GetRelatedItems(ByVal idItem As Integer)
Dim idit As Integer
If ItemIsIncluded(idItem) Then
Set rlinks = CurrentDb().OpenRecordset("Select iditem1, iditem2,
linkweight from linkweight where item1 = " & idItem & " or item2 = " & idIte
m)
While Not rlinks.EOF
If rlinks.iditem1 = idItem Then
idit = rlinks.iditem2
Else
idit = rlinks.iditem1
End If
If rlinks.linkweight < GetItemWeight(idit) Then
IncludeItem (idit)
Else
GetRelatedItems(idit)
End If
rlinks.MoveNext
Wend
End If
End Sub
Sub Main()
GetRelatedItems(ItemID)
End sub
The problem I have is volume: this database could have a million or more
items.
That's why I can't go through this tables with a recursive algorithm.
Can you help me? How could this be done in a better way?See responses inline:
amota wrote:
> Hi,
> I have to go through some tables, like a network. I have a recursive
> algorithm, but I need to do it iteratively.
> Basically, there are 2 tables through which I have to go: Items table
> and Relationship table.
> The tables I have defined are like this:
> - Item table, 2 fields: ItemID, ItemWeight
> - Relationships table, 3 fields: Item1, Item2, LinkWeight.
> Given Item x, I need to get all of the items related to it. It has to
> go
> down through that item's related items if relationship weight is
> greater than item weight.
> It stops
> - when relationship weight is lower than item weight,
> - it has already propagated all of the items
> I have this algorithm:
> I defined 2 global variables:
> - PropagatedItems - contains all of the items that have been
> "visited" and their related items
> - FoundItems: contains the items that were visited but not its
> related items
> Sub GetRelatedItems(ByVal idItem As Integer)
> Dim idit As Integer
> If ItemIsIncluded(idItem) Then
> Set rlinks = CurrentDb().OpenRecordset("Select iditem1, iditem2,
> linkweight from linkweight where item1 = " & idItem & " or item2 = "
> & idItem)
I'm not clear why you are asking an Access question (this is DAO code) in a
SQL Server newsgroup. You run the risk of getting responses that will work
in SQL Server but not in Access. Your better plan would have been to ask in
an Access newsgroup such as microsoft.public.access.queries.
> While Not rlinks.EOF
> If rlinks.iditem1 = idItem Then
> idit = rlinks.iditem2
> Else
> idit = rlinks.iditem1
> End If
> If rlinks.linkweight < GetItemWeight(idit) Then
> IncludeItem (idit)
> Else
> GetRelatedItems(idit)
> End If
> rlinks.MoveNext
> Wend
> End If
> End Sub
> Sub Main()
> GetRelatedItems(ItemID)
> End sub
> The problem I have is volume: this database could have a million or
> more items.
> That's why I can't go through this tables with a recursive algorithm.
> Can you help me? How could this be done in a better way?
How about this:
Select i.ItemID,r1.Item2, r2.Item1
from (Items i inner join linkweight r1 on i.ItemID = r1.Item1)
inner join linkweight r2 on i.ItemId = r2.Item2
Where r1.linkweight > ItemWeight and
r2.linkweight > ItemWeight
order by ItemID
If that does not give you what you want, perhaps you need a union query:
Select i.ItemID,r1.Item2 RelatedItem
from Items i inner join linkweight r1 on i.ItemID = r1.Item1
Where r1.linkweight > ItemWeight
Union
Select i.ItemID,r1.Item1
from Items i inner join linkweight r2 on i.ItemID = r2.Item1
Where r2.linkweight > ItemWeight
order by ItemID
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||"amota" <amota@.discussions.microsoft.com> wrote in message
news:25EB9702-0A72-4961-B115-856788E503D1@.microsoft.com...
> Hi,
> I have to go through some tables, like a network. I have a recursive
> algorithm, but I need to do it iteratively.
>
<snip>
amota,
Please repost your message to the following newsgroup:
microsoft.pulic.access.modulesdaovba
Sincerely,
Chris O.|||I'm just using access as a demo, but I plan to do it with SQL Server.
That's why I posted here in this group.
--
amota
"Chris2" escribió:
> "amota" <amota@.discussions.microsoft.com> wrote in message
> news:25EB9702-0A72-4961-B115-856788E503D1@.microsoft.com...
> <snip>
> amota,
> Please repost your message to the following newsgroup:
> microsoft.pulic.access.modulesdaovba
>
> Sincerely,
> Chris O.
>
>|||So did you try my suggestions ... ?
amota wrote:
> I'm just using access as a demo, but I plan to do it with SQL Server.
> That's why I posted here in this group.
>
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||hmm, no, didnt work.
First one, gives me an empty record set
Second one, doesn't give a complete set.
Also, I asked here because I think it's better to do it as a stored procedur
e.
amota
"Bob Barrows [MVP]" escribió:
> So did you try my suggestions ... ?
> amota wrote:
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||example:
ITEMS TABLE:
ItemID ItemWeight
10 -1
11 0.3
12 1
13 -0.2
Relationships TABLE:
Item1 Item2 linkweight
11 10 0.1
12 10 0.42
12 11 0.5
13 10 0.33
13 12 -0.33
For Item 13:
Correct set: 13, 10, 11, 12
Notice that direct related are 13 and 10
Items related to 10 are 11 and 12
Your first suggestion, gives empty,
the second one, gives 2 records: 13,10 and 13,13
--
amota
"Bob Barrows [MVP]" escribió:
> So did you try my suggestions ... ?
> amota wrote:
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||Look at this example (in T-SQL):
http://milambda.blogspot.com/2005/0...or-monkeys.html
A good hyerarchy begins with a model that prevents circular referencing.
ML|||Again, the syntactical differences between JetSQL and Transact-SQL (SQL
Server's variant of SQL) may prevent suggestions made here from being usable
in your Access prototype. You should consider prototyping with MSDE.
Whether or not a stored procedure is used is somewhat irrelevant to the
question about what is the best sql to use for your task.
Your followup message provides some sample data so i will reply to that one.
amota wrote:
> hmm, no, didnt work.
> First one, gives me an empty record set
> Second one, doesn't give a complete set.
> Also, I asked here because I think it's better to do it as a stored
> procedure.
> --
> amota
>
> "Bob Barrows [MVP]" escribi:
>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||amota wrote:
> example:
> ITEMS TABLE:
> ItemID ItemWeight
> 10 -1
> 11 0.3
> 12 1
> 13 -0.2
> Relationships TABLE:
> Item1 Item2 linkweight
> 11 10 0.1
> 12 10 0.42
> 12 11 0.5
> 13 10 0.33
> 13 12 -0.33
> For Item 13:
> Correct set: 13, 10, 11, 12
> Notice that direct related are 13 and 10
> Items related to 10 are 11 and 12
> Your first suggestion, gives empty,
> the second one, gives 2 records: 13,10 and 13,13
Ah! You have a hierarchy. ML gave one solution. For another solution, read
these articles about using nested sets:
http://www.mvps.org/access/queries/qry0023.htm
http://www.dbmsmag.com/9603d06.html
http://www.intelligententerprise.com/001020/celko.shtml
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
