tag:blogger.com,1999:blog-4282222233168200124.post4388192336596214896..comments2023-11-02T04:19:29.341-04:00Comments on Zen of SQL: Shortest Path for Friend ConnectionsPlamen Ratchevhttp://www.blogger.com/profile/02027705815827955614noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-4282222233168200124.post-24505996012501026122014-04-05T09:16:19.952-04:002014-04-05T09:16:19.952-04:00Hello,
I know this post is *very* old but I need ...Hello,<br /><br />I know this post is *very* old but I need to understand something :<br /><br /> SELECT C.c_from, C.c_to,<br /><br /> CAST(P.c_path + C.c_to + '.' AS VARCHAR(200))<br /><br /> FROM PathCTE AS P<br /><br /> JOIN Contacts AS C<br /><br /> ON P.c_to = C.c_from<br /><br />I don't understand how P.c_path can and where it is defined.<br /><br />Second, is P one step begind the current select result ?<br /><br />P.c_to = C.c_from<br /><br />c_to is before c_from, no ?<br /><br />I don't understand everything out, I am a noob.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-87334899087294358372008-08-18T16:57:00.000-04:002008-08-18T16:57:00.000-04:00Hi Ajay,The case here was to demonstrate directed ...Hi Ajay,<BR/><BR/>The case here was to demonstrate directed connections. If you want to consider all possible combinations (in all directions), that will increase a lot your result set. It is very easy to achieve this, simply add one more query to the CTE to create all possible combinations in both directions (transitive closure for undirected cyclic graph).<BR/><BR/>WITH AllContacts (c_from, c_to)<BR/>AS<BR/>(SELECT c_from, c_to <BR/>FROM Contacts<BR/>UNION ALL<BR/>SELECT c_to, c_from<BR/>FROM Contacts),<BR/>PathCTE <BR/>AS<BR/>(SELECT c_from, c_to,<BR/>CAST('.' + CAST(c_from AS VARCHAR(8)) + '.' +<BR/>CAST(c_to AS VARCHAR(8)) + '.' AS VARCHAR(200)) AS c_path <BR/>FROM AllContacts AS C1 <BR/>UNION ALL <BR/>SELECT C.c_from, C.c_to,<BR/>CAST(P.c_path + C.c_to + '.' AS VARCHAR(200)) <BR/>FROM PathCTE AS P <BR/>JOIN AllContacts AS C <BR/>ON P.c_to = C.c_from <BR/>WHERE P.c_path NOT LIKE '%.' + <BR/>CAST(C.c_from AS VARCHAR(10)) + '.' + <BR/>CAST(C.c_to AS VARCHAR(10)) + '.%')<BR/>INSERT INTO Paths<BR/>SELECT c_path FROM PathCTE;Plamen Ratchevhttps://www.blogger.com/profile/02027705815827955614noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-56289671541830510882008-08-18T16:29:00.000-04:002008-08-18T16:29:00.000-04:00Hi secure place,Did you try the query with the sam...Hi secure place,<BR/><BR/>Did you try the query with the sample data you provided? Here are the results if you have not:<BR/><BR/>c_path<BR/>-----------<BR/>.B.A.C.A.D.<BR/>.B.A.D.<BR/><BR/>The query does not go into endless loop because of the condition in the recursive member of the CTE. The condition WHERE P.c_path NOT LIKE ... prevent the cycles in the path. Since your data has two valid distinct paths that connect B and D you see both.Plamen Ratchevhttps://www.blogger.com/profile/02027705815827955614noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-49098103802685200822008-07-31T11:44:00.000-04:002008-07-31T11:44:00.000-04:00awesome post..but i guess its not connecting prope...awesome post..but i guess its not connecting properly....could you edit the query ..enabling it to consider all the possible connection...right now if A->B & and A->C then its not considering the fact that B->C .<BR/><BR/>except it a very nice post ..u r a true sql master.<BR/><BR/>regards<BR/>AjayAjay Sharmahttps://www.blogger.com/profile/02337309468706584314noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-72889677389264817412008-07-01T17:40:00.000-04:002008-07-01T17:40:00.000-04:00Hi, could you give us an example that is able to ...Hi, could you give us an example that is able to deal with relations like in a real database, in detail:<BR/><BR/>A,C<BR/>C,A<BR/>A,B<BR/>B,A<BR/>A,D<BR/>D,A<BR/><BR/>Path from B->D should be B->A->D<BR/><BR/>The Problem is, it seems with your sql script there is an endless loop :-( But I think you will find a way to solve this issue.<BR/>Everything works well if I do not have duplicated entries in the "from" field but this is a must have...Secure Placehttps://www.blogger.com/profile/15148695423193557890noreply@blogger.com