alex.walmsley (6/23/2016)

---

Hi,

First question. Apologies for newbie errors.

I have a simple mapping table with related records like the following, just using IDs.

(IdA, IdB)

(1,1),

(1,2),

(2,2),

(2,3),

(3,4),

(4,4),

(4,5)

You can see the first 4 records are interlinked. I.e IdA = 1 appears twice, IdB = 2 appears twice and is linked to another IdA (2), which in turn is linked to another IdB. That's the first group because there is then no other relation between any of the other Ids.

So I need to write the query to give an extra column that will group those that are interrelated and give them a group Id, like the following result

(IdA, IdB, GroupId)

(1,1,1),

(1,2,1),

(2,2,1),

(2,3,1),

(3,4,2),

(4,4,2),

(4,5,2)

...

It feels so simple but I can't get it working after trying with self-joins and recursive CTEs. I'm writing a view so I'll be avoiding loops where possible.

Any help out there? Thanks.