SQL Server - use columns from the main query in the subquery
You can user OUTER APPLY
SELECT * FROM tbl1 OUTER APPLY ( SELECT TOP 1 currency_id, SUM(taxrate) AS taxrate FROM tbl2 WHERE wuptr.currency_id = tbl1.currency_id GROUP BY tbl2.currencyid )
You don't need a subquery for that:
SELECT item1, * FROM TableA A INNER JOIN TableB B ON A.item = B.item AND A.x = B.x;
I can't think of a scenario where you would need to JOIN
on a subquery with a filter like that where it wouldn't be equivalent to just reference the field directly in the outer query.
You can reference the outer table in the subquery in the WHERE
clause, though:
SELECT <stuff>FROM Table tWHERE EXISTS (SELECT 1 from TableB B WHERE t.id = b.id)
EDIT
For your actual code, just change the JOIN
criteria to this:
) TBI on (T1.DocEntry = TBI.DocEntry and T1.InstlmntID = TBI.InstallmentID and TBI.DocType = T1.ObjType AND TBI.CompanyDB = T0.UnionAll_Empresa )
If you want to join on to a subquery and "get a column in real-time"/ reference a column from the main query, then there is a trick to doing this.
You can't access the tables which are outside of the subquery if it's used as an aliased table, in other words, this SQL can never access A:
...INNER JOIN ( select * from TableB B where A.item = B.item) on A.x = B.x;
The way to access A would be like this:
SELECT item1, * FROM TableA A INNER JOIN TableB on TableB.item = TableA.item and TableB.item in( select top 1 B.Item from TableB B where A.item = B.item)
Just ignore the "top 1" piece, I just added that to show that there may a reason for doing a join like this.
So, basically if you want to reference an item from the query in the subquery, just move the subquery to the ON section of a join and use the IN keyword as illustrated above.