I should have mentioned that by "equivalent IDs," I meant logically
equivalent, i.e., they identify the same thing. Also, to view my tables
and "trees," please click fixed-font.

--Jeff

jefftyzzer wrote:
> Friends:
>
> In a DB2 UDB LUW table, I have a table with pairs of equivalent ID's.
> What I want to do is assign all equivalent IDs to the same group
> number, including those that are transitively related, i.e., if A = B
> and B = C then A = C, so I'd group all three together.
>
> Although they're not related in a composition fashion per se, it seems
> like the way to go conceptually is to consider the ID relationships as
> a reporting tree (ID_A would be the manager and ID_B would be the
> employee) and assign all IDs that share the same root to the same
> group.
>
> For instance, let's say I have the following pairs
>
> ID_A ID_B
> ---- ----
> 1800 1804
> 1800 1808
> 1806 1809
> 1808 1810
> 1808 1812
> 1809 1815
> 1810 1811
>
> I'd have two trees (sideways):
>
> 1800 1804
> 1808
> 1810
> 1811
> 1812
> and
>
> 1806
> 1809
> 1815
>
> I'm struggling with the following:
>
> 1. How to group based on a shared *root* (I'd hate to have to build a
> chain column, e.g., for 1811: "1800-->1808-->1810" and do something
> like DENSE_RANK() OVER(ORDER BY SUBSTR(CHAIN,1,4)--that seems
> unreliable, and the ID is not always the same length)
>
> 2. How to write a recursive CTE that accomodates multiple, independent,
> trees.
>
> What I'd like to end up with is this:
>
> ID GRP
> ---- ---
> 1800 1
> 1804 1
> 1808 1
> 1810 1
> 1811 1
> 1812 1
> 1806 2
> 1809 2
> 1815 2
>
> I feel like I'm close--I've read Serge's "CONNECT BY" article and
> Molinaro's chapter "Hierarchical Queries" in his _SQL Cookbook_, but
> I'm just not able to stitch it all together.
>
> Would anyone care to lend a hand?
>
> Thanks,
>
> --Jeff