Use columns from the main query in the subquery

Question

Is there any way to get a column in real time, from a main query, and use it in a subquery?

Something like this: (Use A.item in the subquery)

SELECT item1, * 
FROM TableA A 
INNER JOIN 
(
    select * 
    from TableB B 
    where A.item = B.item
) on A.x = B.x;

I need to modify this existing query. It worked before, but now that the database changed, I need to do some modifications, add some comparisons. As you can see there are a lot of JOINS, and one of them is a subquery. I need to add a comparison from a column from the main query (from the table T0 for example) to the subquery (like this: T6.UnionAll_Empresa = T0.UnionALl_Empresa)

Select T0.UnionAll_Empresa,<STUFF>

from [UNION_ALL_BASES]..OINV T0 with (nolock)
inner join [UNION_ALL_BASES]..INV6 T1 with (nolock) on t0.DocEntry = t1.DocEntry and t0.UnionAll_Empresa = t1.UnionAll_Empresa
inner join

(
select 
t1.CompanyID,
T2.CompanyDb,
t1.OurNumber,
T6.BankCode,
T6.BankName,
T3.[Description] Situation,
T1.[Status],
T5.Descrption nomeStatus,
T1.Origin,
T1.DocEntry,
T1.DocType,
T1.ControlKey,
T1.CardCode,
T4.[Description] ContractBank,
T1.PayMethodCode,
T1.DueDate,
T1.DocDate,
T1.InstallmentID,
T1.InstallmentValue,
T1.Correction,
T1.InterestContractural,
T1.FineContract,
T1.ValueAbatment,
T1.ValueDiscount,
T1.ValueFineLate,
T1.ValueInterestDaysOfLate,
T1.OtherIncreases,
T1.ValueInWords,
T1.ValueDocument,
T1.DigitalLine,
T1.Document
from [IntegrationBank]..BillOfExchange T1 with (nolock)
    inner join [InterCompany2]..CompanyHierarchy T2 with (nolock) on T1.CompanyID = T2.ID
    left join [IntegrationBank]..BillOfExchangeSituation T3 with (nolock) on T1.Situation = T3.ID 
    inner join [IntegrationBank]..ContractBank T4 with (nolock) on T1.ContractBank = T4.ID 
    inner join [IntegrationBank]..BoeStatus T5 with (nolock) on T1.[Status] = T5.ID 
    inner join [UNION_ALL_BASES]..ODSC T6 with (nolock) on T4.BankKey = T6.AbsEntry and **T6.UnionAll_Empresa = T0.UnionALl_Empresa** --I need to do this 
where T1.[Status] <> 5 
and T2.CompanyDb = **T0.UnionAll_Empresa** --I need to do this
) TBI on (T1.DocEntry = TBI.DocEntry and T1.InstlmntID = TBI.InstallmentID and TBI.DocType = T1.ObjType )
inner join [UNION_ALL_BASES]..OCTG T2 on T0.GroupNum = T2.GroupNum and T0.UnionAll_Empresa = T2.UnionAll_Empresa
inner join [UNION_ALL_BASES]..OSLP T3 on T0.SlpCode = T3.SlpCode and T0.UnionAll_Empresa = T3.UnionAll_Empresa
where not exists (select 1
        from [UNION_ALL_BASES]..RIN1 A with (nolock) 
                inner join [UNION_ALL_BASES]..ORIN B with (nolock) on A.DocEntry = B.DocEntry and A.UnionAll_Empresa = B.UnionAll_Empresa
        where A.BaseEntry = T0.DocEntry
        and   B.SeqCode = ''1'' )

can you specify what you mean by "get a field in real time"? Real time? What does that mean? — ControlAltDel, Apr 03 '12 at 19:26
You cannot do this because this is not a subquery. This is derived table. Depending on your database you might be able to do that. MS Sql Server has `cross apply / outer apply` for this purposes. More importantly, why do you need it? Why is join not suitable? — Nikola Markovinović, Apr 03 '12 at 19:34
I updated my answer for your actual code. You can just add another condition to your `JOIN` and be done. — JNK, Apr 03 '12 at 19:52

score 45 · Answer 1 · answered Feb 16 '14 at 08:50

45

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
                        )

answered Feb 16 '14 at 08:50

mmmmmm

980
1
14
16

This is actually the correct answer that works for cases where subquery approach cannot be used or is impractical. – Mr. Napik Jan 03 '16 at 22:35

JNK · Answer 2 · 2012-04-03T19:52:23.840

20

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 t
WHERE 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 )

edited Apr 03 '12 at 19:52

answered Apr 03 '12 at 19:24

JNK

63,321
15
122
138

1

Well, this was just an example. The real query is a little complex, and I really would like an approach like the one I said, even if I had to use some sort of global variable or whatever =/ – João Guilherme Apr 03 '12 at 19:30
@JoãoGuilherme I assumed it was a simplification. What exactly do you need this for? `JOIN` conditions exist for scenarios like this. – JNK Apr 03 '12 at 19:34
you could try naming the sub query ie SELECT * FROM a, (SELECT * FROM WHERE a.x=y) b WHERE... You should be able to do what you want. There may be a limit to how deep you can nest if its more than 3 that could be an issue. Looking on the net could it by syntax - on (A.x = B.x) – Alex Apr 03 '12 at 19:36
@Alex - you HAVE to alias it if you are `JOIN`ing a derived table like that, so I don't think that will help very much. – JNK Apr 03 '12 at 19:38
sadly haven't got db conn to try stuff but seems to be stuff you want here: http://www.akadia.com/services/sqlsrv_subqueries.html – Alex Apr 03 '12 at 19:42
@Alex Did you even read the question? He knows how to write a derived table, he wants an outer query reference in a derived table which isn't doable in SS. – JNK Apr 03 '12 at 19:43

score 16 · Answer 3 · answered Apr 20 '12 at 11:14

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.

score 11 · Answer 4 · answered Jul 13 '15 at 09:26

11

You can do this by naming the tables of the main query and the nested query. For example:

SELECT continent, name, population FROM world x
  WHERE population >= ALL
    (SELECT population FROM world y
        WHERE y.continent=x.continent
          AND population>0)

reference: http://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

answered Jul 13 '15 at 09:26

Mohamed Nagieb

819
9
11

1

Reference on the `ALL` keyword https://msdn.microsoft.com/en-us/library/ms178543.aspx?f=255&MSPPError=-2147217396 – PedroC88 Jan 17 '16 at 22:00

score 1 · Answer 5 · answered Jul 12 '17 at 01:06

Not sure why people are over-complicating this. @JNK is correct that you can move the predicate into the main query. For completeness, I will demonstrate.

You have two predicates in your subquery that reference T0:

T6.UnionAll_Empresa = T0.UnionAll_Empresa
T2.CompanyDb = T0.UnionAll_Empresa

The first is an INNER JOIN predicate on the table T6, and the second a WHERE clause - these are both "hard" filters, and will filter out results that don't match (unlike a LEFT OUTER JOIN which will simply set reference to that table's values to NULL).

Well, since T6.UnionAll_Empresa and T2.CompanyDb both need to filter against T0.UnionAll_Empresa, then we can simply change the INNER JOIN predicate on T6 to this:

T2.CompanyDb = T6.UnionAll_Empresa

Then, we can remove the WHERE clause in the subquery, and we can add this JOIN predicate to TBI in the main query:

TBI.CompanyDb = T0.UnionAll_Empresa

...making the entire query this:

Select T0.UnionAll_Empresa,<STUFF>

from [UNION_ALL_BASES]..OINV T0 with (nolock)
inner join [UNION_ALL_BASES]..INV6 T1 with (nolock) on t0.DocEntry = t1.DocEntry and t0.UnionAll_Empresa = t1.UnionAll_Empresa
inner join
(
    select 
    t1.CompanyID,
    T2.CompanyDb,
    t1.OurNumber,
    T6.BankCode,
    T6.BankName,
    T3.[Description] Situation,
    T1.[Status],
    T5.Descrption nomeStatus,
    T1.Origin,
    T1.DocEntry,
    T1.DocType,
    T1.ControlKey,
    T1.CardCode,
    T4.[Description] ContractBank,
    T1.PayMethodCode,
    T1.DueDate,
    T1.DocDate,
    T1.InstallmentID,
    T1.InstallmentValue,
    T1.Correction,
    T1.InterestContractural,
    T1.FineContract,
    T1.ValueAbatment,
    T1.ValueDiscount,
    T1.ValueFineLate,
    T1.ValueInterestDaysOfLate,
    T1.OtherIncreases,
    T1.ValueInWords,
    T1.ValueDocument,
    T1.DigitalLine,
    T1.Document
    from [IntegrationBank]..BillOfExchange T1 with (nolock)
    inner join [InterCompany2]..CompanyHierarchy T2 with (nolock) on T1.CompanyID = T2.ID
    left join [IntegrationBank]..BillOfExchangeSituation T3 with (nolock) on T1.Situation = T3.ID 
    inner join [IntegrationBank]..ContractBank T4 with (nolock) on T1.ContractBank = T4.ID 
    inner join [IntegrationBank]..BoeStatus T5 with (nolock) on T1.[Status] = T5.ID 
    inner join [UNION_ALL_BASES]..ODSC T6 with (nolock) on T4.BankKey = T6.AbsEntry and T2.CompanyDb = T6.UnionAll_Empresa
    where T1.[Status] <> 5 
) TBI on (T1.DocEntry = TBI.DocEntry and T1.InstlmntID = TBI.InstallmentID and TBI.DocType = T1.ObjType and TBI.CompanyDb = T0.UnionAll_Empresa)
inner join [UNION_ALL_BASES]..OCTG T2 on T0.GroupNum = T2.GroupNum and T0.UnionAll_Empresa = T2.UnionAll_Empresa
inner join [UNION_ALL_BASES]..OSLP T3 on T0.SlpCode = T3.SlpCode and T0.UnionAll_Empresa = T3.UnionAll_Empresa
where not exists (
    select 1
    from [UNION_ALL_BASES]..RIN1 A with (nolock) 
    inner join [UNION_ALL_BASES]..ORIN B with (nolock) on A.DocEntry = B.DocEntry and A.UnionAll_Empresa = B.UnionAll_Empresa
    where A.BaseEntry = T0.DocEntry
    and B.SeqCode = ''1''
)

This is entirely equivalent to what you have, and removes any reference to T0 from your subquery.

Use columns from the main query in the subquery

5 Answers5