I have a collection of relationship rows of the form
Table: graph
key1 varchar
key2 varchar
A row of the form ('a','b') indicates that 'a' and 'b' are related.
The table contains many relationships between keys, forming several
disjoint sets. All relationships are bi-directional, and both
directions are present. I.e. the table contains a set of disjoint
graphs specified as node pairs.
For example the set of values
key1 key2
----- -----
a x
a y
b w
c t
x a
y a
y z
z y
t c
w b
w d
d w
defines three disjoint groups of connected keys:
a x y z
c t
b w d
What I would like to achieve is a single SQL query that returns
group key
----- ---
1 a
1 x
1 y
1 z
2 c
2 t
3 b
3 w
3 d
I don't care about preserving the node-to-node relationships, only
the group membership for each node.
I've been playing with "WITH RECURSIVE" CTEs but haven't had any
success. I'm not really sure how to express what I want in SQL, and
it's not completely clear to me that recursive CTEs will help here.
Also I'm not sure how to generate the sequence numbers for the groups