Correlated Subquery inside a join?

Question

I have a query where I need a correlated subquery inside a join. I say need, what I mean is that I think I need it there... How can I do this? My query is below, I get the "The multi-part identifier "FIELDNAME GOES HERE" could not be bound" error... How can I change this query to work?

SELECT FireEvent.ExerciseID, 
       FireEvent.FireEventID, 
       tempHitEvent.HitEventID, 
       FireEvent.AssociatedPlayerID, 
       tempHitEvent.AssociatedPlayerID, 
       FireEvent.EventTime, 
       tempHitEvent.EventTime, 
       FireEvent.Longitude, 
       FireEvent.Latitude, 
       tempHitEvent.Longitude, 
       tempHitEvent.Latitude, 
       tempHitEvent.HitResult, 
       FireEvent.AmmunitionCode, 
       FireEvent.AmmunitionSource, 
       FireEvent.FireEventID, 
       0 AS 'IsArtillery' 
FROM   FireEvent 
       LEFT JOIN (SELECT HitEvent.*, 
                         FireEvent.FireEventID, 
                         Rank() 
                           OVER ( 
                             ORDER BY HitEvent.EventTime) AS RankValue 
                  FROM   HitEvent 
                         WHERE FireEvent.EventTime BETWEEN 
                                    Dateadd(millisecond, -5000, 
                                    HitEvent.EventTime) AND 
                                               Dateadd(millisecond, 
                                               5000, HitEvent.EventTime) AND HitEvent.FiringPlayerID = FireEvent.PlayerID 
                   AND HitEvent.AmmunitionCode = 
                       FireEvent.AmmunitionCode
                   AND HitEvent.ExerciseID = 
                       'D289D508-1479-4C17-988C-5F6A847AE51E' 
                        AND FireEvent.ExerciseID = 
                       'D289D508-1479-4C17-988C-5F6A847AE51E' 
                   AND HitEvent.HitResult NOT IN ( 0, 1 ) ) AS 
                 tempHitEvent 
              ON ( 
              RankValue = 1
            AND tempHitEvent.FireEventID = 
                     FireEvent.FireEventID 
                     )
WHERE  FireEvent.ExerciseID = 'D289D508-1479-4C17-988C-5F6A847AE51E' 
ORDER BY HitEventID

Wait, could your problem just be in the order by clause, where you need to have an prefix for HitEventID ? — Gordon Linoff
– Gordon Linoff, Commented May 30, 2012 at 13:40

GarethD · Accepted Answer · 2012-05-30 14:05:51Z

Use OUTER APPLY instead of LEFT JOIN. I have had to move some of your clauses around, but the below should produce the desired result

SELECT  FireEvent.ExerciseID, 
        FireEvent.FireEventID, 
        tempHitEvent.HitEventID, 
        FireEvent.AssociatedPlayerID, 
        tempHitEvent.AssociatedPlayerID, 
        FireEvent.EventTime, 
        tempHitEvent.EventTime, 
        FireEvent.Longitude, 
        FireEvent.Latitude, 
        tempHitEvent.Longitude, 
        tempHitEvent.Latitude, 
        tempHitEvent.HitResult, 
        FireEvent.AmmunitionCode, 
        FireEvent.AmmunitionSource, 
        FireEvent.FireEventID, 
        0 AS 'IsArtillery' 
FROM    FireEvent 
        OUTER APPLY
        (   SELECT  HitEvent.*, RANK() OVER (ORDER BY HitEvent.EventTime) AS RankValue 
            FROM    HitEvent 
            WHERE   HitEvent.FireEventID = FireEvent.FireEventID 
            AND     FireEvent.EventTime BETWEEN DATEADD(MILLISECOND, -5000, HitEvent.EventTime) AND DATEADD(MILLISECOND, 5000, HitEvent.EventTime) 
            AND     HitEvent.FiringPlayerID = FireEvent.PlayerID 
            AND     HitEvent.AmmunitionCode = FireEvent.AmmunitionCode
            AND     HitEvent.ExerciseID = FireEvent.ExerciseID
            AND     HitEvent.HitResult NOT IN ( 0, 1 ) 
        ) AS tempHitEvent 
WHERE   COALESCE(RankValue, 1) = 1
AND     FireEvent.ExerciseID = 'D289D508-1479-4C17-988C-5F6A847AE51E' 
ORDER BY FireEvent.HitEventID

If you only want to return results where there is a matching HitEvent use CROSS APPLY. CROSS APPLY is to OUTER APPLY what INNER JOIN is to LEFT JOIN.

ADDENDUM

This can all be achieved using joins with no need for OUTER APPLY, by moving not using a subquery to join HitEvent, then performing the RANK function on all data, not just the HitEvent table. This all needs to be moved to a subquery so the result of the RANK function can be inlcuded in a WHERE clause.

SELECT  *
FROM    (   SELECT  FireEvent.ExerciseID, 
                    FireEvent.FireEventID, 
                    HitEvent.HitEventID, 
                    FireEvent.AssociatedPlayerID, 
                    --HitEvent.AssociatedPlayerID, 
                    FireEvent.EventTime, 
                    HitEvent.EventTime [HitEventTime], 
                    FireEvent.Longitude [FireEventLongitute], 
                    FireEvent.Latitude [FireEventLatitute], 
                    HitEvent.Longitude [HitEventLongitute], 
                    HitEvent.Latitude [HitEventLatitute], 
                    HitEvent.HitResult , 
                    FireEvent.AmmunitionCode, 
                    FireEvent.AmmunitionSource,
                    0 [IsArtillery],
                    RANK() OVER(PARTITION BY HitEvent.FireEventID, HitEvent.FiringPlayerID, HitEvent.AmmunitionCode,HitEvent.ExerciseID ORDER BY HitEvent.EventTime) [RankValue]
            FROM    FireEvent 
                    LEFT JOIN HitEvent
                        ON HitEvent.FireEventID = FireEvent.FireEventID 
                        AND FireEvent.EventTime BETWEEN DATEADD(MILLISECOND, -5000, HitEvent.EventTime) AND DATEADD(MILLISECOND, 5000, HitEvent.EventTime) 
                        AND HitEvent.FiringPlayerID = FireEvent.PlayerID 
                        AND HitEvent.AmmunitionCode = FireEvent.AmmunitionCode
                        AND HitEvent.ExerciseID = FireEvent.ExerciseID
                        AND HitEvent.HitResult NOT IN ( 0, 1 ) 
        ) data
WHERE   RanKValue = 1

This may offer slightly improved performance compared to using OUTER APPLY, it may not. It depends on your schema and the amount of data you are processing. There is no substitute for testing:

Faraday · Accepted Answer · 2012-05-30 02:11:06Z

Cross Apply works in this scenario...

SELECT FireEvent.ExerciseID, 
       FireEvent.FireEventID, 
       tempHitEvent.HitEventID, 
       FireEvent.AssociatedPlayerID, 
       tempHitEvent.AssociatedPlayerID, 
       FireEvent.EventTime, 
       tempHitEvent.EventTime, 
       FireEvent.Longitude, 
       FireEvent.Latitude, 
       tempHitEvent.Longitude, 
       tempHitEvent.Latitude, 
       tempHitEvent.HitResult, 
       FireEvent.AmmunitionCode, 
       FireEvent.AmmunitionSource, 
       FireEvent.FireEventID, 
       0 AS 'IsArtillery' 
       ,RankValue
FROM   FireEvent
       CROSS APPLY  (SELECT HitEvent.*, 
                         FireEvent.FireEventID, 
                         Rank() 
                           OVER ( 
                             ORDER BY HitEvent.EventTime) AS RankValue 
                  FROM   HitEvent 
                         WHERE FireEvent.EventTime BETWEEN 
                                    Dateadd(millisecond, -5000, 
                                    HitEvent.EventTime) AND 
                                               Dateadd(millisecond, 
                                               5000, HitEvent.EventTime) AND HitEvent.FiringPlayerID = FireEvent.PlayerID 
                   AND HitEvent.AmmunitionCode = 
                       FireEvent.AmmunitionCode
                   AND HitEvent.ExerciseID = 
                       'D289D508-1479-4C17-988C-5F6A847AE51E' 
                        AND FireEvent.ExerciseID = 
                       'D289D508-1479-4C17-988C-5F6A847AE51E' 
                   AND HitEvent.HitResult NOT IN ( 0, 1 ) )  
                 tempHitEvent 
              WHERE 
              RankValue = 1
            AND tempHitEvent.FireEventID = 
                     FireEvent.FireEventID 

AND  FireEvent.ExerciseID = 'D289D508-1479-4C17-988C-5F6A847AE51E' 
ORDER BY HitEventID

Gordon Linoff · Accepted Answer · 2012-05-30 13:07:47Z

0

You don't need the correlated subquery. You seem to have a relatively simple join, albeit on several fields at the same time.

The following FROM clause extracts the conditions from inside the subquery that refer to both tables and move them outside into the JOIN clause. This comes close to what you want, depending on whether the partition in the rank is doing what you expect:

FROM FireEvent fe left outer join
     (SELECT HitEvent.*, 
             Rank() OVER (partition by FiringPlayerID, ExerciseID, FireEventID, FireEventID
                          ORDER BY HitEvent.EventTime) AS RankValue 
      FROM HitEvent 
      WHERE HitEvent.ExerciseID = 'D289D508-1479-4C17-988C-5F6A847AE51E' AND  
            HitEvent.HitResult NOT IN ( 0, 1 )
     ) he
     ON he.FiringPlayerID  = fe.PlayerId and
        he.AmmunitionCode = fe.AmmunitionCode and
        fe.EventTime BETWEEN Dateadd(millisecond, -5000, he.EventTime) AND 
                             Dateadd(millisecond, 5000, he.EventTime) AND
        fe.ExerciseID = 'D289D508-1479-4C17-988C-5F6A847AE51E' AND
        RankValue = 1 , d
        he.FireEventID =  fe.FireEventID

edited May 30, 2012 at 13:07

answered May 30, 2012 at 2:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

GarethD Over a year ago

This would not work, by applying the conditions within the subquery the results of the RANK function are selected. i.e. By using Outer apply the subquery will return the first event with 5 seconds of the fireevent.EventTime, however your query will only return a result if the first HitEvent is within 5 seconds of the FireEvent time (There is a subtle, but important difference).

Gordon Linoff Over a year ago

I modified the query to have a better partition clause in the rank(). This is not exactly the same. However, I am thinking that you can unroll the query by just taking the subquery, adding another column (using a window function) to calculate fire event id, and dispense with the outer join entirely.

GarethD Over a year ago

Modifying the partion clause has helped to a certain extent, however a fundamental problem still remains that the results of the subquery are directly related to the main query so a correlated subquery is required. I have done an SQL Fiddle to demonstrate the problem better. In the 2nd results pane the HitResult is NULL because the first HitEvent is not within 5 seconds of the FireEvent. In the top query the first HitEvent within 5 seconds is displayed (with HitResult = 4 because this occurred before the row with HitResult = 5).

Gordon Linoff Over a year ago

Before the advent of window functions, all correlated subqueries could be written as joins and group bys. What you are saying is that you need the join to FE in the subquery. You can add that back in, if one of the "apply" methods doesn't work.

GarethD Over a year ago

I am not saying that using a correlated subquery is the only way, what I am saying is the query you have posted does not work in the same way as the logic proposed in the question i.e. the first hitevent within 5 seconds of the fireevent.

Collectives™ on Stack Overflow

Correlated Subquery inside a join?

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related