It's been a long time since I've done more than the most basic sql queries. But I ran into this one today and have spent a few hours on it and am stuck with my derived table attempt (this is for an Oracle db). Looking for a few tips. Thx.
TABLE: dtree
DataID Name
-------------
10001 A.doc
10002 B.doc
10003 C.doc
10004 D.doc
TABLE: collections
CollectionID DataID
---------------------
201 10001
201 10002
202 10003
203 10004
TABLE: rimsNodeClassification
DataID RimsSubject RimsRSI Status
---------------------------------------
10001 blah IS-03 Active
10002 blah LE-01 Active
10003 blah AD-02 Active
10004 blah AD-03 Active
TABLE: rsiEventSched
RimsRSI RetStage DateToUse RetYears
--------------------------------------
IS-03 SEM-PHYS 95 1
IS-03 ACT NULL 2
LE-01 SEM-PHYS 94 1
LE-01 INA-PHYS 95 2
LE-01 ACT NULL NULL
LE-01 OFC NULL NULL
LE-02 SEM-PHYS 94 2
Trying to query on CollectionID=201
INTENDED RESULT:
DataID Name RimsRSI Status SEMPHYS_DateToUse INAPHYS_DateToUse SEMPHYS_RetYears INAPHYS_RetYears
-------------------------------------------------------------------------------------------------------
10001 A.doc IS-03 Active 95 null 1 null
10002 B.doc Le-01 Active 94 95 1 2
You don't need a Derived Table, just join the tables (the last using a Left join) and then apply a MAX(CASE) aggregation:
select c.DataID, t.Name, rnc.RimsRSI, rnc.Status,
max(case when res.RetStage = 'SEM-PHYS' then res.DateToUse end) SEMPHYS_DateToUse,
max(case when res.RetStage = 'INA-PHYS' then res.DateToUse end) INAPHYS_DateToUse,
max(case when res.RetStage = 'SEM-PHYS' then res.RetYears end) SEMPHYS_RetYears,
max(case when res.RetStage = 'INA-PHYS' then res.RetYears end) INAPHYS_RetYears
from collections c
join dtree t
on c.DataID = t.DataID
join rimsNodeClassification rnc
on c.DataID = rnc.DataID
left join rsiEventSched res
on rnc.RimsRSI = res.RimsRSI
where c.CollectionID= 201
group by c.DataID, t.Name, rnc.RimsRSI, rnc.Status