Select single "child" row from complex query
-
I am sure this question has been asked a million times before, but I am drawing a complete blank.
I need to retrieve the earliest date from the tblSavedForm (tblSavedForm.SavedDate) in the query below:
Select TM_ASSN_Assignment.ASSN_AssnNum, TD_RECV_Recovery.RECV_Date, tblSavedForm.SavedDate, tblSavedForm.FormID, tblClientForm.ClientFormTitle FROM #ValidAssignments INNER JOIN TM_ASSN_Assignment on assn_ids = ASSN_ID INNER JOIN TM_ASNA_AssignmentAsset ON ASNA_FK_ASSN_ID = ASSN_ID INNER JOIN TD_RECV_Recovery ON RECV_FK_ASST_ID = ASNA_FK_ASSN_ID AND RECV_Date Between @DateStart AND @DateEnd Left Outer JOIN tblSavedForm ON MainFileID = ASSN_ID AND FormID IN ( select ClientFormID FROM tblClientForm WHERE ClientFormTitle LIKE 'CR -%') Left Outer JOIN tblClientForm ON FormID = ClientFormID
My problem is that the tblSavedForm may return multiple rows.
Thank you,
Bryan
-
Without your table definitions, or sample data its difficult, but try this
Select TM_ASSN_Assignment.ASSN_AssnNum,
TD_RECV_Recovery.RECV_Date,
min(tblSavedForm.SavedDate),
tblSavedForm.FormID,
tblClientForm.ClientFormTitle
FROM #ValidAssignments
INNER JOIN TM_ASSN_Assignment
on assn_ids = ASSN_ID
INNER JOIN TM_ASNA_AssignmentAsset
ON ASNA_FK_ASSN_ID = ASSN_ID
INNER JOIN TD_RECV_Recovery
ON RECV_FK_ASST_ID = ASNA_FK_ASSN_ID
AND RECV_Date Between @DateStart AND @DateEnd
Left Outer JOIN tblSavedForm
ON MainFileID = ASSN_ID
AND FormID IN ( select ClientFormID
FROM tblClientForm
WHERE ClientFormTitle LIKE 'CR -%')
Left Outer JOIN tblClientForm
ON FormID = ClientFormID
Group by TM_ASSN_Assignment.ASSN_AssnNum,TD_RECV_Recovery.RECV_Date,tblSavedForm.FormID,tblClientForm.ClientFormTitle
-
Unfortunately, I can not include the tblSavedForm.FormID in my grouping. I may have 2 different FormID's that match my ClientFormTitle criteria.
-
...
FROM #ValidAssignments
INNER JOIN TM_ASSN_Assignment
on assn_ids = ASSN_ID
INNER JOIN TM_ASNA_AssignmentAsset
ON ASNA_FK_ASSN_ID = ASSN_ID
INNER JOIN TD_RECV_Recovery
ON RECV_FK_ASST_ID = ASNA_FK_ASSN_ID
AND RECV_Date Between @DateStart AND @DateEnd
Left outer Join (select Min(FormId) as MinFormId, ClientFormId, MainFieldId from tblSavedForm
group by ClientFormId, MainFieldId 
SF1 on SF.MainFileID = ASSN_ID Left Outer JOIN tblSavedForm SF ON SF.FormID = SF1.MinFormId and SF.MainFieldId = SF1.MainFieldId and SF.ClientFormId= SF1.ClientFormId
Left Outer JOIN tblClientForm
ON FormID = ClientFormID and ClientFormTitle LIKE 'CR -%'
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply