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 :)