0

I have two tables:

CompanyCases is a table of companies and case numbers like this:

CompanyId, CaseNumber

CaseRelations is a table of cases and their related cases like this:

CaseNumber, RelatedCase

There can be several companies related to one case, and one case can be related to several companies.

I need a query that will give me all related cases for a company id. The trick is that when I find the related case, that can also have a related case, which can have a related case etc.

My first assumption was that it would not be that deep, so I could just do self joins like:

Select 
cc.CompanyId,
cc.CaseNumber,
CR1.CaseNumber,
CR1.RelatedCase,
CR2.CaseNumber,
CR2.RelatedCase

FROM CompanyCases cc
LEFT JOIN CaseRelations CR1 ON CR1.CaseNumber = cc.CaseNumber
LEFT JOIN CaseRelations CR2 ON CR2.CaseNumber = CR1.RelatedCase

And then keep joining as many levels as is needed. The problem is that the cases loop. So it can go like this:

CaseNumber    RelatedCase
1                 2
2                 3
3                 1

So I can keep joining forever without reaching a full column of nulls. Also it is at least 5 levels deep so this is not a great solution. I don't mind using recursive CTEs either but I think I will get the same problem with the circular cases.

I hope I described it well enough - Does anyone know how to solve this?

Thanks in advance :)

  • 2
    One approach to avoiding getting stuck in a cycle it to keep track of the path followed and look to see if the current node looks familiar. An example with a recursive CTE is [here](http://stackoverflow.com/a/15081353/92546). – HABO Nov 08 '16 at 20:26
  • Can you add a table of your desired output please? – iamdave Nov 10 '16 at 12:26

0 Answers0