As previously said, you can use a cross apply.

I'm leaving both options. Note that the join needs to calculate all the values before joining and that's why it can be slow.

SELECT TOP 5

x.Result

FROM dbo.MyTable1 t1

CROSS APPLY (SELECT SUM(lt2.col3) AS Result

FROM dbo.MyTable2 lt2

WHERElt2.col1 = lt.col1 AND lt2.col2 = lt.col2 AND lt2.id = lt.id ) x;

SELECT TOP 5

x.Result

FROM dbo.MyTable1 t1

JOIN (SELECT lt2.col1, lt2.col2, lt2.id,

SUM(lt2.col3) AS Result

FROM dbo.MyTable2 lt2

GROUP BY lt2.col1, lt2.col2, lt2.id ) x ON x.col1 = lt.col1 AND x.col2 = lt.col2 AND x.id = lt.id;