CODEWITHSUNDEEP
sqlsql-servert-sql
ojhawkins
ojhawkins

Reputation: 3278

Join Subquery in FROM clause aliased in WHERE clause

How can I move the join that is currently in the WHERE clause AND subMainTable.ID = MainTable.ID into the the FROM clause.

I cannot seem to move it there due to the way it is aliased.

This is a working query which I have renamed and removed unrelated parts to the question.

SELECT  
      ID
     ,DETAILS
     ,ENTRYDATE
     ,ISNULL(LastBalance, 0) + (
           SELECT SUM(VAL)
           FROM tempData subMainTable
                LEFT OUTER JOIN FinanceTable f ON subMainTable.ID = f.accId
                                                AND subMainTable.TRANS_ID = f.txnID
           WHERE subMainTable.TRANS_ID <= MainTable.TRANS_ID
                     AND subMainTable.ID = MainTable.ID
                     AND f.txnID IS NULL
          ) AS Balance
FROM 
     (SELECT MainTable.*
      FROM tempData MainTable LEFT JOIN FinanceTable FinTable ON 
                   (MainTable.ID = FinTable.accId
                    AND MainTable.TRANS_ID = FinTable.txnID )
      WHERE  (FinTable.accId IS NULL AND FinTable.txnID IS NULL )
                ) AS MainTable ...

Upvotes: 0

Views: 422

Answers (2)

paparazzo
paparazzo

Reputation: 45096

This is part of it 

  SELECT  ID
         ,DETAILS
         ,ENTRYDATE
         ,ISNULL(LastBalance, 0) + (
                SELECT SUM(VAL)
                  FROM tempData subMainTable
                  LEFT OUTER JOIN FinanceTable f 
                    ON subMainTable.ID = f.accId
                   AND subMainTable.TRANS_ID = f.txnID
                   AND subMainTable.TRANS_ID <= MainTable.TRANS_ID
                   AND subMainTable.ID = MainTable.ID
                 WHERE f.txnID IS NULL
                ) AS Balance
    FROM tempData MainTable 
    LEFT JOIN FinanceTable FinTable 
      ON MainTable.ID = FinTable.accId
     AND MainTable.TRANS_ID = FinTable.txnID
   WHERE FinTable.accId IS NULL 
     AND FinTable.txnID IS NULL

This is a try at all

  SELECT  ID
         ,DETAILS
         ,ENTRYDATE
         ,ISNULL(LastBalance, 0) 
         ,SUM(VAL) AS DeltaBalance
    FROM tempData MainTable 
    LEFT JOIN FinanceTable FinTable 
      ON MainTable.ID = FinTable.accId
     AND MainTable.TRANS_ID = FinTable.txnID
    LEFT JOIN subMainTable 
      ON subMainTable.ID = MainTable.ID
     AND subMainTable.TRANS_ID <= MainTable.TRANS_ID  
    LEFT JOIN FinanceTable f 
      ON subMainTable.ID = f.accId
     AND subMainTable.TRANS_ID = f.txnID
   WHERE FinTable.accId IS NULL 
     AND FinTable.txnID IS NULL
     AND f.txnID IS NULL
   GROUP BY ID, DETAILS,ENTRYDATE,LastBalance

Upvotes: 1

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

Try something like :

SELECT  MainTable.ID,MainTable.DETAILS,MainTable.ENTRYDATE,ISNULL(MainTable.LastBalance, 0) + ( SUM(subMainTable.VAL)) AS Balance
FROM    
(
    SELECT MainTable.*
    FROM   tempData MainTable
    LEFT JOIN FinanceTable FinTable ON (MainTable.ID = FinTable.accId
    AND MainTable.TRANS_ID = FinTable.txnID
    )
    WHERE  (FinTable.accId IS NULL  AND FinTable.txnID IS NULL  )
) AS MainTable 
left outer join
(
    SELECT TRANS_ID ,ID , SUM(VAL) 
    FROM 
    tempData subMainTable LEFT OUTER JOIN FinanceTable f 
    ON 
    subMainTable.ID = f.accId AND 
    subMainTable.TRANS_ID = f.txnID
    WHERE 
    f.txnID IS NULL
) as subMainTable on subMainTable.TRANS_ID <= MainTable.TRANS_ID AND subMainTable.ID = MainTable.ID
group by MainTable.ID,MainTable.DETAILS,MainTable.ENTRYDATE,ISNULL(MainTable.LastBalance, 0)

I did't tested it but i think it will work .

Upvotes: 1

Related Questions