Re: Complex Recursive Query

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general