title: Query a table for Related Old Reports
body: <p>I am back to this issue. Here is my table with some records: </p>
  
  <pre><code>CREATE TABLE [dbo].[test](
      [reportID] [int] NOT NULL,
      [caseID] [int] NOT NULL,
      [carriedOver] [bit] NULL,
      [oldReportID] [int] NULL
  ) ON [PRIMARY]
  GO
  INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
  VALUES (304, 4, 1, NULL)
  GO
  INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
  VALUES (305, 4, 0, NULL)
  GO
  INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
  VALUES (306, 4, 1, 304)
  GO
  INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
  VALUES (309, 4, 1, 306)
  GO
  INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
  VALUES (311, 4, 0, NULL)
  GO
  INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
  VALUES (312, 4, 1, 309)
  GO
  INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
  VALUES (100, 1, 0, NULL)
  GO
  INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
  VALUES (418, 8, 0, NULL)
  GO
  INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
  VALUES (436, 8, 1, NULL)
  GO
  INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
  VALUES (438, 8, 0, 436)
  GO
  </code></pre>
  
  <p>Desired output is as follows :</p>
  
  <p>For CaseID = 4, I should get back ReportIDs : 304, 306, 309 and 312</p>
  
  <p>For CaseID = 1, I should get back only ReportID : 100</p>
  
  <p>For CaseID = 8, I should get back only ReportID : 418</p>
  
  <p>It is basically getting a chain of carried reportIDs when the first report is carried over along with what it is carried into. If the first report is not carried over, then it should return only that report.</p>
  
  <p>A suggested solution was:  </p>
  
  <pre><code>declare @caseID int = 4
  SELECT t.reportID
  FROM dbo.test t
   WHERE ( exists ( SELECT 1 FROM dbo.test t1 WHERE t1.reportID = t.oldReportID and t1.caseID = @caseID ) or
           exists ( SELECT 1 FROM dbo.test t2 WHERE t2.oldReportID = t.reportID and t2.caseID = @caseID )  )
  and caseID = @caseID
  ORDER BY 1 asc
  </code></pre>
  
  <p>This works fine for CaseID=4, but it fails for CaseID=1 or CaseID=8.</p>
  
  <p>Any suggestions? </p>