select idTopic, author, deleted, idText, datePosted
from
(
select t.idTopic, t.author, t.deleted, x.idText, x.datePosted,
row_number() over (partition by t.idTopic order by x.datePosted desc) rn
from topics t
left join texts x on t.idTopic = x.idTopic -- or INNER JOIN
) z
where rn=1
Check out the ROW_NUMBER() function which is used to give a unique sequential number to each row in each "partition". In this case partition by t.idTopic tells the function to independently number each set of records of the same t.idTopic.
Within each partition, the records are numbered 1,2,3... based on a specific order, which in this case is order by x.datePosted desc - this puts the newest posts first.
LEFT JOIN also known in full as "LEFT OUTER JOIN" is used to keep all records of the table on the left side even if there are no records from the right. If you don't need to see a topic if there are no texts for it, simply drop the "LEFT" keyword.
The query is moved into a derived table and aliased (unimportantly as z) so that we can filter on it for where rn = 1. Recall that each partition can only have one row of value rn=1, which is by definition of our ORDER BY clause in ROW_NUMBER(), the row with the latest text.
;WITH First_table
AS (SELECT '2015-03-08' AS [Date],'Diag1' AS Diagnostics, 2932 AS ID
UNION ALL SELECT '2015-03-09','Diag2', 2932
)
,Second_table
AS (SELECT '2015-03-09' AS [Date], 'Eval1' AS Evaluation ,2932 AS ID
UNION ALL SELECT '2015-03-09','Eval2',2932
UNION ALL SELECT '2015-03-10' ,'Eval3' , 2932)
SELECT
KT.[Date] AS [Date]
,FT.Diagnostics AS [Diagnostics]
,STUFF(ST.Evaluation,1,1,'') AS [Evaluation]
FROM
(
SELECT [Date] ,[ID]
FROM First_table
--GROUP BY [Date] ,[ID]
UNION
SELECT [Date] ,[ID]
FROM Second_table
--GROUP BY [Date] ,[ID]
) AS KT --keyTAble
LEFT JOIN First_table AS FT
ON kt.[Date] = FT.[Date]
AND kt.[ID]= FT.[ID]
OUTER APPLY
(
SELECT
',' + Evaluation
FROM Second_table AS ST
WHERE
KT.[DATE] =ST.[Date]
AND kT.[ID]= ST.[ID]
FOR XML PATH('')
)ST(Evaluation)
Best Answer
Normally this is done by creating a view
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15