I'm having trouble trying to code a SQL update statement that interestingly enough follows the scenario very similar to the 'Six Degrees of Separation' premise.

Consider that we have a table with the following data. We want to update the ACQUINTANCE column starting with KevinBacon. The only way I've been able to do this is by using a loop and going up the acquintance level one at a time. Is it at all possible to do this in one elegant and efficient SQL statement?

PERSON A    PERSON B    ACQUINTANCE?
John        Jane        ?
Josh        June        ?
Mark        June        ?
Mark        Gail        ?
Rick        Gail        ?
Rick        Kyra        ?
KevinBacon    Kyra        ?

The target result set is:

PERSON A    PERSON B    ACQUINTANCE?
John        Jane        No
Josh        June        Yes
Mark        June        Yes
Mark        Gail        Yes
Rick        Gail        Yes
Rick        Kyra        Yes
KevinBacon    Kyra        Yes -- jvjean24601 ------------------------------------------------------------------------ Posted via http://www.codecomments.com ------------------------------------------------------------------------

Have a look at

http://toponewithties.blogspot.com/2005/03/path-enumeration-using-prime-number.html

Show quoteHide quote

http://toponewithties.blogspot.com/2005/03/path-enumeration-using-prime-number.html
Roji. P. Thomas,

When I execute the script to build the Prime Numbers Table (found
on:
http://toponewithties.blogspot.com/2005/03/prime-numbers-table-and-insert-script.html),
it returns the error:

..Net SqlClient Data Provider: Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'BEGIN'.


The following line appears to have a missing element:

WHILE @cnt <>

What element is meant to come after "<>"?

Was it supposed to be 1000?


Sincerely,

Chris O.

You have Recursive Queries with SQL-2005:
http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/

Show quoteHide quote

If this is a tree, then look up "Nested Sets Model" and do  it in one
query.  The gimmick is building a forest and clustering it into trees.
if you started with ("John", "Jane"), then the results woudl be
reversed.   What you want is a column called "Acquaintance Group"
number or somesuch. 

Also get a copy of TREES & HIERARCHIES IN SQL.

The nested set method is:-

    1)    Difficult to maintain
    2)    Does not scale nor perform well out of the lab and in the real
world.

A better method is that developed by Itzik Ben Gan, information here:
http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=8826&DisplayTab=Article

Show quoteHide quote

how do I prevent duplicate ID in my situation
Date issue
DB Design issue
Help selecting maximum number of characters
How to calculate comparisons of fields between records