| Groups | Blog | Home |
sql server (microsoft) : How to do a Recursive Join with SQL
Table1 contains: Key,Name 1, A 2, B 3, C Table 2 contains: Fkey, Name2 1, B 1, D 2, C 2, E So i want to say something like "Show me all the links for A" giving: A,B A,D A,C A,E The reason we get A,C and A,E is because A is linked to B and then B is linked to C and E. So A is indirectly linked to these last two. This was be quite simple to right in a procedural language. But how would you write it in SQL? Can SQLServer do this? Thanks.
Yes this kind of query can be done, in fact rather easily --- assuming
you're using SQL Server 2005. Recursive queries in SQL sever 2005 are done using common table expressions --- the basic syntax goes something like this: with yourCTE(yourKey, yourName) as ( SELECT root row FROM yourTable UNION ALL SELECT childRow from yourTable JOIN yourCTE on yourTable.parentColumn = yourCTE.keyColum ) select * from yourCTE This is a vague example (mostly because youre schema is also rather vague) I have a 3 minute video on my community web site (http:// www.ifoundtime.com/community) that you can download and look at. It uses the employee hierarchy of the AdventureWorks sample database to illistrate the nested relationships. Hope that helps. Ralph Shillington iFoundTime Inc. [quoted text, click to view] On Apr 5, 12:17 pm, "Herby" <prmarjo...@gmail.com> wrote: > I have two tables that can be joined on a foreign key. > > Table1 contains: > > Key,Name > 1, A > 2, B > 3, C > > Table 2 contains: > > Fkey, Name2 > 1, B > 1, D > 2, C > 2, E > > So i want to say something like "Show me all the links for A" > > giving: > > A,B > A,D > A,C > A,E > > The reason we get A,C and A,E is because A is linked to B and then B > is linked to C and E. > So A is indirectly linked to these last two. > > This was be quite simple to right in a procedural language. > > But how would you write it in SQL? > Can SQLServer do this? > > Thanks.
Thanks Ralph, that gives me something to work with.
Sorry if my tables are vague, I thought i was making them simple.
[quoted text, click to view]
Herby wrote: > I have two tables that can be joined on a foreign key. > > Table1 contains: > > Key,Name > 1, A > 2, B > 3, C > > > Table 2 contains: > > Fkey, Name2 > 1, B > 1, D > 2, C > 2, E > > So i want to say something like "Show me all the links for A" > > giving: > > A,B > A,D > A,C > A,E > > The reason we get A,C and A,E is because A is linked to B and then B > is linked to C and E. > So A is indirectly linked to these last two. > > This was be quite simple to right in a procedural language. > > But how would you write it in SQL? > Can SQLServer do this? If you don't have SQL Server 2005, and trust that there's an upper bound on the number of levels you'll need to recurse, then you can do something like: select t1.Name, t2.Name2 from Table1 t1 join Table2 t2 on t1.Key = t2.Fkey union select t1.Name, t2b.Name2 from Table1 t1 join Table2 t2 on t1.Key = t2.Fkey join Table1 t1b on t2.Name2 = t1b.Name join Table2 t2b on t1b.Key = t2b.Fkey
Thanks Ed, that i can understand a bit better, but the levels could go
alot deeper than that - Again the depth of the dependencies is another question that would be really useful to ask on the structure for a given row. It could return anything from 0 to ... well within reason would not ever expect this to be deeper than 64. For record A on Table1 it would return A depends on B and B depends on C so the depth is 2( I think! ). So for each row would return the following pairs: A,2 B,1 C,0 Just in this simple example there is much information to be extracted. Initially i am going to use SQLServer, but would like solutions for other databases later. Ralph, from Eds example he clearly understands the structure of my two simple tables. Are you able to map this more specifically onto the general solution you provided? Thanks.
[quoted text, click to view]
On Apr 5, 8:12 pm, "rshillington" <rshilling...@gmail.com> wrote: > Yes this kind of query can be done, in fact rather easily --- assuming > you're using SQL Server 2005. Recursive queries in SQL sever 2005 are > done using common table expressions --- the basic syntax goes > something like this: > > with yourCTE(yourKey, yourName) as > ( > SELECT root row FROM yourTable > UNION ALL > SELECT childRow from yourTable JOIN yourCTE on > yourTable.parentColumn = yourCTE.keyColum > ) > select * from yourCTE > > This is a vague example (mostly because youre schema is also rather > vague) I have a 3 minute video on my community web site (http://www.ifoundtime.com/community) that you can download and look at. It > uses the employee hierarchy of the AdventureWorks sample database to > illistrate the nested relationships. > > Hope that helps. > Ralph Shillington > iFoundTime Inc. > > On Apr 5, 12:17 pm, "Herby" <prmarjo...@gmail.com> wrote: > > Ralph your recursive expression is only recursing over a single table. What makes mine more complex is that it is in fact something called indirect recursion via the joining of the two tables. So does CTE support this. So im thinking your solution for my example problem would translate into something like the following: With NestedDepends( name1, name2 ) as ( select Name, Name2 from Table1 join Table2 on key = fkey where Name = 'A' union all select Name, Name2 from Table1 join Table2 on key = fkey join NestedDepends Table1.Name = NestedDepends.name1 ) This kind of SQL does not come naturally to me, but im trying. Can someone help me with the above as it is not quite right but feel i am getting close. Thanks.
[quoted text, click to view]
Herby wrote: > Ralph your recursive expression is only recursing over a single > table. What makes mine more complex is that it is in fact something > called indirect recursion via the joining of the two tables. So does > CTE support this. > > So im thinking your solution for my example problem would translate > into something like the following: > > With NestedDepends( name1, name2 ) as > ( > select Name, Name2 from Table1 join Table2 on key = fkey where Name > = 'A' > union all > select Name, Name2 from Table1 join Table2 on key = fkey > join NestedDepends Table1.Name = NestedDepends.name1 > ) I don't have SS2005 to test, but if that doesn't work, then the next thing I would try is creating a view: create view NameLinks as select t1.Name, t2.Name2 from Table1 t1 join Table2 t2 on t1.key = t2.fkey
[quoted text, click to view]
On Apr 8, 10:37 pm, Ed Murphy <emurph...@socal.rr.com> wrote: > Herby wrote: > > Ralph your recursive expression is only recursing over a single > > table. What makes mine more complex is that it is in fact something > > called indirect recursion via the joining of the two tables. So does > > CTE support this. > > > So im thinking your solution for my example problem would translate > > into something like the following: > > > With NestedDepends( name1, name2 ) as > > ( > > select Name, Name2 from Table1 join Table2 on key = fkey where Name > > = 'A' > > union all > > select Name, Name2 from Table1 join Table2 on key = fkey > > join NestedDepends Table1.Name = NestedDepends.name1 > > ) > > I don't have SS2005 to test, but if that doesn't work, then the next > thing I would try is creating a view: > > create view NameLinks as > select t1.Name, t2.Name2 > from Table1 t1 join Table2 t2 on t1.key = t2.fkey > > and then using Ralph's WITH expression on the view.- Hide quoted text - > > - Show quoted text - Thanks Ed will try that.
Im getting somewhere with the following ammended query:
With NestedDepends( name1, name2 ) as ( select Name, Name2 from Table1 join Table2 on key = fkey where Name = 'A' union all select Name, Name2 from Table1 join Table2 on key = fkey join NestedDepends on Table1.Name = NestedDepends.name1 ) select * from NestedDepends OPTION(MAXRECURSION 0); The last clause allows removes a depth constraint on the recursion which defaults to 100. My problem is it keeps repeating all the rows indefinately e.g: A,B A,C A,E A,B A,C A,E and so on... Why is it doing this? and how can i stop it repeating the same sets? Thanks.
No the recursion is not working alls im getting repeated is:
A,B A,D A,B A,D and so on... A is the row i want to know all the transitive dependencies? Can anyone help me? if this were procedural it would be something as simple as: void GetDepends( list ) { for each item in dependsList{ list.Add( item.Name ) item.GetDepends( list ) } } How can i do the above in SQL ???
[quoted text, click to view]
On Apr 6, 4:09 am, Ed Murphy <emurph...@socal.rr.com> wrote: > Herby wrote: > > I have two tables that can be joined on a foreign key. > > > Table1 contains: > > > Key,Name > > 1, A > > 2, B > > 3, C > > > Table 2 contains: > > > Fkey, Name2 > > 1, B > > 1, D > > 2, C > > 2, E > > > So i want to say something like "Show me all the links for A" > > > giving: > > > A,B > > A,D > > A,C > > A,E > > > The reason we get A,C and A,E is because A is linked to B and then B > > is linked to C and E. > > So A is indirectly linked to these last two. > > > This was be quite simple to right in a procedural language. > > > But how would you write it in SQL? > > Can SQLServer do this? > > If you don't have SQL Server 2005, and trust that there's an upper > bound on the number of levels you'll need to recurse, then you can > do something like: > > select t1.Name, t2.Name2 > from Table1 t1 > join Table2 t2 on t1.Key = t2.Fkey > union > select t1.Name, t2b.Name2 > from Table1 t1 > join Table2 t2 on t1.Key = t2.Fkey > join Table1 t1b on t2.Name2 = t1b.Name > join Table2 t2b on t1b.Key = t2b.Fkey > > and so on, if you need more than two levels.- Hide quoted text - > > - Show quoted text - Ed, this works iv just run it and i get the right rows. Can this not be translated into a CTE so that it work at more levels? Thanks
[quoted text, click to view]
Herby wrote: > Ed, this works iv just run it and i get the right rows. > Can this not be translated into a CTE so that it work at more levels?
[quoted text, click to view]
On Apr 13, 2:42 am, Ed Murphy <emurph...@socal.rr.com> wrote: > Herby wrote: > > Ed, this works iv just run it and i get the right rows. > > Can this not be translated into a CTE so that it work at more levels? > > Don't know, haven't messed with CTEs. Did you try using a view? Ed im not clear how you think the view could help? As it is only substituting the original select statement. Even though you do not have SQLServer 2005, could you give me a pseudo example of what you think you mean? And then i will try it out. Thanks.
[quoted text, click to view]
On Apr 13, 2:42 am, Ed Murphy <emurph...@socal.rr.com> wrote: > Herby wrote: > > Ed, this works iv just run it and i get the right rows. > > Can this not be translated into a CTE so that it work at more levels? > > Don't know, haven't messed with CTEs. Did you try using a view? Ok created a view of the following ParentChild SELECT dbo.Table1.Name1 AS parent, dbo.Table2.Name2 AS child FROM dbo.Table1 INNER JOIN dbo.Table2 ON dbo.Table1.PKey = dbo.Table2.FKey with prnt_chld_cte (parent, child, dist_from_parent) AS ( select parent, child, 1 as dist_from_parent from ParentChild where parent = 'A'' union all select pcc.parent, pc.child, dist_from_parent + 1 from ParentChild pc inner join prnt_chld_cte pcc on pc.parent = pcc.child ) select parent, child, dist_from_parent from prnt_chld_cte order by 1, 3, 2 OPTION (MAXRECURSION 0); And its working, at last thanks for everyone's help. I have a new set of problems now, Loops, with the real dataset! How to detect loops? Thanks again.
Don't see what you're looking for? Try a search.
|
March 2003
April 2003
May 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |