Joining subquery using a column of outer query

Question

This is my join query:

Select * 
    From MetricAlertMonitorings maa
        Left Join (
                    Select Top 1 *
                        From [dbo].MetricAlertMonitoringsDeliveredLog
                            Where maa.MetricAlertMonitoringID = [dbo].MetricAlertMonitoringsDeliveredLog.MetricAlertMonitoringID
                            Order By DeliveredDateTime asc
                ) mdl
                    ON maa.MetricAlertMonitoringID = mdl.MetricAlertMonitoringID
                Left Join (
                    Select Top 1 *
                        From [dbo].MetricAlertMonitoringsAcknowledgedLog
                            Where maa.MetricAlertMonitoringID = [dbo].MetricAlertMonitoringsAcknowledgedLog.MetricAlertMonitoringID
                            Order By MetricAlertMonitoringsStatusID asc, AcknowledgedDateTime asc
                ) mal
                    ON maa.MetricAlertMonitoringID = mal.MetricAlertMonitoringID

But somehow the query is not able to recognize column maa.MetricAlertMonitoringID. I get error:

Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "maa.MetricAlertMonitoringID" could not be bound.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "maa.MetricAlertMonitoringID" could not be bound.

I need to join my query as a subquery. Any idea how do I fix this?

Answer 1

A joined subquery cannot reference a column from the outer query, hence the error that you are getting. I think that you ought to use OUTER APPLY instead:

Select * 
From MetricAlertMonitorings maa
outer apply (
    Select Top 1 *
    From [dbo].MetricAlertMonitoringsDeliveredLog
    Where maa.MetricAlertMonitoringID = [dbo].MetricAlertMonitoringsDeliveredLog.MetricAlertMonitoringID
    Order By DeliveredDateTime asc
) mdl
outer apply (
    Select Top 1 *
    From [dbo].MetricAlertMonitoringsAcknowledgedLog
    Where maa.MetricAlertMonitoringID = [dbo].MetricAlertMonitoringsAcknowledgedLog.MetricAlertMonitoringID
    Order By MetricAlertMonitoringsStatusID asc, AcknowledgedDateTime asc
) mal