SQL Server - use columns from the main query in the subquery SQL Server - use columns from the main query in the subquery sql-server sql-server

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.