How do I get the carried over reports + the very first report from the following table?
Result should be : 304, 306, 309, and 312.
x
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
After a little bit of tinkering, the solution needed some tweaking when there is only one caseID.
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID])
VALUES (100, 1, 0, NULL)
GO
declare @caseID int = 1
SELECT t.reportID, tr.*, t.*
FROM dbo.test t
INNER JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY reportID) AS RowNum,
reportID
FROM dbo.test
WHERE caseID = @caseID ) tr on tr.reportID = t.reportID
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 ) or
tr.rowNum < 2 )
and caseID = @caseID
ORDER BY 1 asc
Advertisement
Answer
You can use EXISTS
:
select t.*
from test t
where exists (select 1 from test t1 where t.oldreportid = t1.reportid) or
exists (select 1 from test t1 where t1.oldreportid = t.reportid);