Try
...
SELECT '' as CarePlanDesc,
dbo.Nursing_CarePlans_DX.DX_Text,
dbo.Nursing_CarePlans_DX.DisplayOrder * 100,
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_DX.DisplayOrder * 100 + 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
...>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 Results>
>select 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,DisplayOrder
>
>
>
Current Query Results>
>>CarePlanDesc 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.00000
>
>
>
>Desired Query Results
>>
>CarePlanDesc 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
>
>
--sb--
