SQL Server Version is 2000
Hoping someone is bored and has a way to turn this data from three tables into single result set. I apologize for the length of this, but here is the current code I'm using, and not getting the desired results. I have posted the current results after this query text as well as the desired results. The current query brings back all the records, but not in the order I want. Basicly I need to interleave the results from the Nursing_CarePlans_InterV table so they fall after their parent record in the Nursing_CarePlans_DX table.
So it there is 1 record in the Nursing_CarePlans_DX table, their might be 3 child records in the Nursing_CarePlans_InterV table for that DX.
Thanks in advance.
Current Query Resultsselect substring(CarePlanDesc,1,25) as CarePlanDesc,
substring(FieldText,1,30) as FieldText,
FieldOrder,
LevelID,
IDFieldValue,
DisplayOrder
from
(
SELECT dbo.Nursing_CarePlans.CarePlanDesc,
' ' as FieldText,
0 as FieldOrder,
dbo.Nursing_CarePlans.LevelID,
dbo.Nursing_CarePlans.NursingCPID as IDFieldValue,
dbo.Nursing_CarePlans.isactive,
cast(1 as Decimal(10,5)) as DisplayOrder
FROM dbo.NoteDic INNER JOIN
dbo.Nursing_CarePlans ON dbo.NoteDic.NursingCPID = dbo.Nursing_CarePlans.NursingCPID INNER JOIN
dbo.Nursing_CarePlans_DX ON dbo.Nursing_CarePlans.NursingCPID = dbo.Nursing_CarePlans_DX.NursingCPID INNER JOIN
dbo.Nursing_CarePlans_InterV ON dbo.Nursing_CarePlans_DX.NursingCP_DX_ID = dbo.Nursing_CarePlans_InterV.NursingCP_DX_ID
WHERE dbo.NoteDic.NoteID=@NoteID
union
SELECT '' as CarePlanDesc,
dbo.Nursing_CarePlans_DX.DX_Text,
dbo.Nursing_CarePlans_DX.DisplayOrder,
dbo.Nursing_CarePlans_DX.LevelID,
dbo.Nursing_CarePlans_DX.NursingCP_DX_ID as IDFieldValue,
dbo.Nursing_CarePlans_DX.isactive,
cast(2 as Decimal(10,5)) as DisplayOrder
FROM dbo.NoteDic INNER JOIN
dbo.Nursing_CarePlans ON dbo.NoteDic.NursingCPID = dbo.Nursing_CarePlans.NursingCPID INNER JOIN
dbo.Nursing_CarePlans_DX ON dbo.Nursing_CarePlans.NursingCPID = dbo.Nursing_CarePlans_DX.NursingCPID INNER JOIN
dbo.Nursing_CarePlans_InterV ON dbo.Nursing_CarePlans_DX.NursingCP_DX_ID = dbo.Nursing_CarePlans_InterV.NursingCP_DX_ID
WHERE dbo.NoteDic.NoteID=@NoteID
union
SELECT '' as CarePlanDesc,
dbo.Nursing_CarePlans_InterV.InterV_Text,
dbo.Nursing_CarePlans_InterV.DisplayOrder,
dbo.Nursing_CarePlans_InterV.LevelID,
dbo.Nursing_CarePlans_InterV.NursingCP_Interv_ID as IDFieldValue,
dbo.Nursing_CarePlans_InterV.isactive,
3 as DisplayOrder
FROM dbo.NoteDic INNER JOIN
dbo.Nursing_CarePlans ON dbo.NoteDic.NursingCPID = dbo.Nursing_CarePlans.NursingCPID INNER JOIN
dbo.Nursing_CarePlans_DX ON dbo.Nursing_CarePlans.NursingCPID = dbo.Nursing_CarePlans_DX.NursingCPID INNER JOIN
dbo.Nursing_CarePlans_InterV ON dbo.Nursing_CarePlans_DX.NursingCP_DX_ID = dbo.Nursing_CarePlans_InterV.NursingCP_DX_ID
WHERE dbo.NoteDic.NoteID=@NoteID)det
order by LevelID,DisplayOrderCurrent Query ResultsCarePlanDesc FieldText FieldOrder LevelID IDFieldValue DisplayOrder
Intra-OP Care Plan 0 1 2 1.00000
Intra Op DX1 1 2 9 2.00000
Intra Op Dx2 2 2 10 2.00000
DX 1 Intervention 2 2 3 21 3.00000
dx 2 Intervention 1 1 3 22 3.00000
dx 2 intervention 2 2 3 23 3.00000
DX1 Intervention 1 1 3 20 3.00000Desired Query ResultsCarePlanDesc FieldText FieldOrder LevelID IDFieldValue
Intra-OP Care Plan 0 1 2
Intra Op DX1 1 2 9
DX1 Intervention 1 1 3 20
DX 1 Intervention 1 2 3 21
Intra Op Dx2 2 2 10
dx 2 Intervention 1 1 3 22
dx 2 intervention 2 2 3 23
