Problem joining tables in SQL

Question

SELECT MID, FAD.FirstOpenedDate ,LCD.LastCloseDate
FROM mwMaster.dbo.Merchant M
JOIN        (
            SELECT MerchID, MIN(moddate) AS FirstOpenedDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   FAD ON FAD.MerchID = M.MerchID 
LEFT JOIN   (
            SELECT MerchID, MAX(moddate) AS LastCloseDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   LCD ON LCD.MerchID = M.MerchID
JOIN        (
            SELECT merchid ,avg(Transactions) ,avg(Profit) 
            FROM mwMaster.dbo.ResidualSummary RS
            WHERE RS.Date_Processed < LCD.LastCloseDate
            GROUP BY Merchid    
            )   R ON R.MerchID = M.MerchID

I am having trouble performing the following join. I have run into this problem before and used temp tables but would like to find out what I am doing wrong. Basically the line that is not working is the 3rd to last. The "< LCD.LastClostDate" says that it cannot be bound. Is it possible to use the value from LCD which I created in a nested query above (in that query I used the M table in a similar way but I didnt run into any issue)? I am thinking becasue the LCD table is dynamically created here it cannot be used in the nested query but this is just my guess.

Any ideas?

On a side note I have also seen people using a CROSS and OVER. Not to farmiliar with how this works but may be applicable here?

Can you explain in plain English what you're trying to achieve here'? — marc_s, Aug 11 '11 at 19:18
Join in a table by using a nested query and use one of the columns in the table created above in a where clause in the select statment — JBone, Aug 11 '11 at 19:20
@marc - looks like the issue is that he wants to use a reference to a subquery from within another subquery, basically. — JNK, Aug 11 '11 at 19:20
Is the last join even needed? I don't see you referencing any columns from the `R` subquery. — Joe Stefanelli, Aug 11 '11 at 19:22
Is there some reason you can't combine the first two subselects? they select from the same relation and group the same column. — SingleNegationElimination, Aug 11 '11 at 19:23
@Joe Stefanelli Since its an INNER JOIN technically there is a reference — Conrad Frix, Aug 11 '11 at 19:25
The actual query is much more complicated I just took out a small piece to try and paint a picture — JBone, Aug 11 '11 at 19:26
@Conrad Frix: Right, but if the columns aren't needed, we can eliminate the overhead of calculating the aggregates and possibly improve things with a `WHERE EXISTS...` instead of a `JOIN`. — Joe Stefanelli, Aug 11 '11 at 19:28
You have to wrap it to make the whole thing a subquery to use LCD.LastCloseDate — SQLMason, Aug 11 '11 at 19:34

Conrad Frix · Accepted Answer · 2011-08-11T19:35:33.877

I think though haven't tested you can just change your JOIN to a CROSS APPLY in SQL 2005+

SELECT MID, FAD.FirstOpenedDate ,LCD.LastCloseDate
FROM mwMaster.dbo.Merchant M
JOIN        (
            SELECT MerchID, MIN(moddate) AS FirstOpenedDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   FAD ON FAD.MerchID = M.MerchID 
LEFT JOIN   (
            SELECT MerchID, MAX(moddate) AS LastCloseDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   LCD ON LCD.MerchID = M.MerchID
CROSS APPLY(
        SELECT merchid ,avg(Transactions) ,avg(Profit) 
        FROM mwMaster.dbo.ResidualSummary RS
        WHERE RS.Date_Processed < LCD.LastCloseDate
        GROUP BY Merchid    
        )   R ON R.MerchID = M.MerchID

But it might be easier to use CTEs

 WITH LCD AS (SELECT MerchID, MAX(moddate) AS LastCloseDate
        FROM mwMaster.dbo.MerchantStatusHistory
        GROUP BY MerchID),
  R AS (
              SELECT merchid ,avg(Transactions) ,avg(Profit) 
              FROM mwMaster.dbo.ResidualSummary RS
                   INNER JOIN LCD on 
                   LCD.MERCHID = RS.MERCHID
              WHERE RS.Date_Processed < LCD.LastCloseDate
              GROUP BY Merchid    
            )

SELECT MID, FAD.FirstOpenedDate ,LCD.LastCloseDate
FROM mwMaster.dbo.Merchant M
JOIN        (
            SELECT MerchID, MIN(moddate) AS FirstOpenedDate
            FROM mwMaster.dbo.MerchantStatusHistory
            GROUP BY MerchID
            )   FAD ON FAD.MerchID = M.MerchID 
LEFT JOIN LCD ON LCD.MerchID = M.MerchID
LEFT JOIN R ON R.MerchID = M.MerchID

Agreed, I generally find CTEs the easiest/simplest way to do this. Although, why not include the remaining sub-select (`MIN(moddate)`) in the `LCD` CTE? The join criteria is the same (same table and columns, `LEFT JOIN LCD` pointless as a `JOIN` is previously required), can be part of the same table/index lookup. — Clockwork-Muse, Aug 11 '11 at 20:08

score 2 · Answer 2 · answered Aug 11 '11 at 19:32

I can't really test this without your data, but here's one way you could do it:

SELECT MID,
       MIN(moddate) OVER (PARTITION BY MerchID) as FirstOpenedDate,
       MAX(moddate) OVER (PARTITION BY MerchID) as LastCloseDate
FROM mwMaster.dbo.Merchant
HAVING DateProcessed < LastCloseDate

Problem joining tables in SQL

2 Answers2