CODEWITHSUNDEEP
sqlsql-servert-sqljoinsql-server-2017
Mrug
Mrug

Reputation: 501

Joining subquery using a column of outer query

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?

Upvotes: 0

Views: 48

Answers (1)

GMB
GMB

Reputation: 222402

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

Upvotes: 3

Related Questions