0

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 ...
Improve this question

2 Answers 2

Reset to default
1

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 .

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.