Generate linked series

Hi,
I would like to generate the following:
suppose we have the following table:
Table1:
Col1 Col2
a b
a c
d e
f c
d b
Then the result should give the following series:
b, a, c
a, b, d
a, c, f
e, d, b
b, a, c, f
Anyone an idea what would be the fastest way to resolve this?
Thanx in advance...
PeterThere are no built-in SQL statements which generates such series. The sample
data somewhat resembles hierarchical data representation. Please search
google for "Nested Sets" and "Path Enumeration" which are two commonly used
approaches to tackle such problems.
Anith|||You need to provide more info about what you want... I had a hard time even
discerning a pattern between the table data and the desired output, and the
one pattern I seemed to get doesn't make any sense...
(because of the fact that you have both "b, a, c" AND "b, a, c,, f" in
the output...)
"PeterM" wrote:

> Hi,
> I would like to generate the following:
> suppose we have the following table:
> Table1:
> Col1 Col2
> a b
> a c
> d e
> f c
> d b
> Then the result should give the following series:
> b, a, c
> a, b, d
> a, c, f
> e, d, b
> b, a, c, f
> Anyone an idea what would be the fastest way to resolve this?
> Thanx in advance...
> Peter
>|||Hi CBretana,
The logic behind it is the following:
I try to search for all possible length "paths" (until the longest path is
found);
these are created by using each row and see if one of the two columns in the
row also exist in another row, if this is the case I concatenate the result
as a "path" (and where the "link" value is only used once in the string).
I hope you see the point?
Peter
"CBretana" wrote:
> You need to provide more info about what you want... I had a hard time eve
n
> discerning a pattern between the table data and the desired output, and th
e
> one pattern I seemed to get doesn't make any sense...
> (because of the fact that you have both "b, a, c" AND "b, a, c,, f" in
> the output...)
> "PeterM" wrote:
>|||Hi,
Let me just explain where this would be useful:
by using the sp_fkeys on all tables in a relational design, I could build a
result table containing rows with all the PK_Table and their related FK_Tabl
e
info.
With this table I would search for all possible "paths" within my relational
design no matter the "depth" of the "path".
In this way I could easily generate, no matter which tables I would give as
input, the shortest path to connect these input tables together and make a
dynamic join statement.
That's why I'm really interested in a solution (preferably also performant).
Peter
"CBretana" wrote:
> You need to provide more info about what you want... I had a hard time eve
n
> discerning a pattern between the table data and the desired output, and th
e
> one pattern I seemed to get doesn't make any sense...
> (because of the fact that you have both "b, a, c" AND "b, a, c,, f" in
> the output...)
> "PeterM" wrote:
>|||Peter,
Don't you want to restrict the generation of these paths to go in one
direction only? what I mean is that a value in Column 1 of some row of the
table that also exists in Column 1 of another record is an intrinsically
different kind of relationship than when it exists in Column 2 of some other
row in the table.. Using the example you describe in yr oother post, one
column is PKs, and other would be FKs, right ? In that case would you ever
WANT to create "Path" that connects two values becase they were both in the
FK column of the table ' That would not imply any relationship at all...
"PeterM" wrote:
> Hi CBretana,
> The logic behind it is the following:
> I try to search for all possible length "paths" (until the longest path is
> found);
> these are created by using each row and see if one of the two columns in t
he
> row also exist in another row, if this is the case I concatenate the resul
t
> as a "path" (and where the "link" value is only used once in the string).
> I hope you see the point?
> Peter
> "CBretana" wrote:
>|||CBretana,
No I don't want any restriction because it is perfectly possible that two
tables are lnked to eachother by their FK_Table; how else would you represen
t
many to many relationships?
Peter
"CBretana" wrote:
> Peter,
> Don't you want to restrict the generation of these paths to go in one
> direction only? what I mean is that a value in Column 1 of some row of th
e
> table that also exists in Column 1 of another record is an intrinsically
> different kind of relationship than when it exists in Column 2 of some oth
er
> row in the table.. Using the example you describe in yr oother post, one
> column is PKs, and other would be FKs, right ? In that case would you ev
er
> WANT to create "Path" that connects two values becase they were both in t
he
> FK column of the table ' That would not imply any relationship at all...
> "PeterM" wrote:
>|||The right way to represent a many-to-many relationship is with a thord table
containing a multple-column (composite) primary Key, consisting of the Both
Primary Keys from the two other tables that have the many-to-many
relationship.
In this third table there is a single Composite PK, and 2 Foreign Keys, one
each to the PK of each of the two original tables.
"PeterM" wrote:
> CBretana,
> No I don't want any restriction because it is perfectly possible that two
> tables are lnked to eachother by their FK_Table; how else would you repres
ent
> many to many relationships?
> Peter
> "CBretana" wrote:
>|||CBrentana,
The third table you're talking about is exactly the FK_table of my example.
If you would run sp_fkeys on the PK_Tables the result would be two rows:
PK_Table1 - FK_Table
PK_Table2 - FK_Table
By linking the result of these two rows you would find the path to link
PK_Table1 with PK_Table2 (through the FK_Table in common).
which is exactly the point of depart.
So I hope you understand the question now (since you confirmed the point of
the many-to-many relationships).
Look I'm more interested in a solution of my question than in trying to
convince you in the "usefullness" of my question, if this would still be you
r
concern.;-)
Thanx...
Peter
"CBretana" wrote:
> The right way to represent a many-to-many relationship is with a thord tab
le
> containing a multple-column (composite) primary Key, consisting of the Bot
h
> Primary Keys from the two other tables that have the many-to-many
> relationship.
> In this third table there is a single Composite PK, and 2 Foreign Keys, on
e
> each to the PK of each of the two original tables.
>
> "PeterM" wrote:
>|||CBrentana,
In reviewing your answers I noticed we're both using different terms.
You're talking about FK_columns, where I'm talking about FK_Tables.
The columns of my examples contain Tables (PK_Tables or FK_Tables) and not
PK-FK columns (that would indeed make no sense). So instead of thinking in
terms of columns change to tables and you'll see that it makes sense :-)
Peter
"PeterM" wrote:
> CBrentana,
> The third table you're talking about is exactly the FK_table of my example
.
> If you would run sp_fkeys on the PK_Tables the result would be two rows:
> PK_Table1 - FK_Table
> PK_Table2 - FK_Table
> By linking the result of these two rows you would find the path to link
> PK_Table1 with PK_Table2 (through the FK_Table in common).
> which is exactly the point of depart.
> So I hope you understand the question now (since you confirmed the point o
f
> the many-to-many relationships).
> Look I'm more interested in a solution of my question than in trying to
> convince you in the "usefullness" of my question, if this would still be y
our
> concern.;-)
> Thanx...
> Peter
>
> "CBretana" wrote:
>