I have two tables
First table(DynamicIndicatorID(P),Question_ID,Quarter,Year,District)
P stands for primary key and F stands for foreign key
second table(fileID,FileName,DynamicIndicatorID(F),Datecreated)
Now In my first table I have four rows just like that
(1 , 1, 1, 1 , 1)
(2 , 1, 1, 2 , 1)
(3 , 1, 2, 1 , 1)
(4 , 1, 1, 1 , 2)
and in my second table I have three rows , my second table basically containing the files for each DynamicIndicatorID , it can contain Zero OR Many rows for each DynamicIndicatorID
just like below
second Table
(1,First.Docx,1,3/3/2001),
(2,New.Docx,1,3/3/2016),
(3,second.Docx,2,3/3/2001),
Now I want to write a query which can give me all records from first table {i.e. four Rows}
I want to add the FileName column as well so I need this record
(DynamicIndicatorID,Question_ID,Quarter,Year,District,FileName)
(1,1,1,1,1,New.Docx),
(2,1,1,2,1,Second.Docx),
(3,1,1,1,1,Nothing),
(4,1,1,1,1,Nothing),
I need to show all rows from first table and I want to show the latest file for each indicator just like above i have two files for DynamicIndicatorID=1 i am getting the latest one New.Docx , and for DynamicIndicatorID=2 I just have one so i am showing that and for DynamicIndicatorID 2,3 I have not any file so I have to show nothing , The mean idea is that I want to get all rows from First table and I want to show the latest file for each dynamicindicatorID , every DynamicIndicatorID must have at most one file which should be the latest one .
What I have tried:
i have tried that , it is giving the good result the latest file for each indicator but it is not showing the other two records which dont have any file
below is my query
SELECT Distinct DI.DynamicIndicatorID,Di.District,tpl.filenameName ,Di.Quarter,DI.District,DI.TFA,DI.TFB,DI.TFG,DI.TFW,DI.TFM from DynamicIndicatorsTargetsForPu
AS DI
Left Outer Join tblUploadedFiledetailsForPartners as tpl on tpl.IndicatorID=DI.DynamicIndicatorID
Where tpl.fileID=(Select max(fileID) from tblUploadedFiledetailsForPartners as tpl where tpl.IndicatorID=DI.DynamicIndicatorID) AND DI.UserId=61