SQL where clause in nested query with column that doesnt exist in nested query

Question

I wrote the following query and it works:

select * from processed p
inner join data d on d.dataId = p.dataid
where assetid in (select a.assetid from dbo.file f
                  inner join asset a on a.assetName = f.fileNumber
                  inner join configMapping c on c.assetId = a.assetid
                  WHERE p.id in (2,11) and rawConfig = 1)

However, I realised this shouldn't work. The query should be written as:

select * from processed p
inner join data d on d.dataId = p.dataid
where assetid in (select a.assetid from dbo.file f
                  inner join asset a on a.assetName = f.fileNumber
                  inner join configMapping c on c.assetId = a.assetid
                  WHERE rawConfig = 1)
and p.id in (2,11)

The nested query is only looking for assetid's. Furthermore, running the nested query on its own wouldn't work because id isn't in those three tables:

select a.assetid from dbo.file f
inner join asset a on a.assetName = f.fileNumber
inner join configMapping c on c.assetId = a.assetid
WHERE p.id in (2,11) and rawConfig = 1

Msg 207, Level 16, State 1, Line 44

Invalid column name 'id'.

Apologies for not having testable data to provide, but essentially the question is how does the first query's nested query use a where on a column from its parent query. Logically I would think the nested query would fail and thus the whole query should fail.

I'm using the newest version of SSMS

A sub-query may step out and look for columns found there. But from the outside you can only reference columns returned from a sub-query, not directly from tables inside a sub-query. — jarlh
– jarlh, Commented Sep 25, 2019 at 8:48
sample data is helpful for clear to understand what you actually want — Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin, Commented Sep 25, 2019 at 8:54

Shushil Bohara · Accepted Answer · 2019-09-25 09:20:26Z

2

You have to add condition in the select using case I have shifted the ContractDate but if it doesn't work then you have to shift other conditions too

Select
    SUM(case when c.ContractDate >= @ContractDate then c.Price else 0 end) As 'TotalPrice', 
    SUM(case when c.ContractDate >= @ContractDate then 1 else 0 end) As 'ContractCount'     
from Rsv_Contract as c
where c.RegisterUsersId = @RegisterUsersId 
    and c.contractstate in (1,2) 
    and c.ContractNumber!='0000000' 
    and c.ContractNumber!='-1'
Group by CAST(c.ContractDate AS DATE)

answered Sep 25, 2019 at 9:20

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

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

Comments

Arif Sher Khan · Accepted Answer · 2019-09-25 11:32:17Z

1

You can use EXISTS clause of sql.

select *
  from processed p
  join data d 
    on d.dataid = p.dataid
 where p.id in (2, 11)
   and exists (select 1
                 from dbo.file f
                 join asset a 
                   on a.assetname = f.filenumber
                 join configmapping c 
                   on c.assetid = a.assetid
                where a.assetid = p.asset_id
                  and rawconfig = 1)

Sorry guys, my bad not mentioning the code beforehand.

Hope this will solve your problem.

edited Sep 25, 2019 at 11:32

answered Sep 25, 2019 at 8:53

Arif Sher Khan

6096 silver badges17 bronze badges

1 Comment

derHugo Over a year ago

Could you extend this comment a bit into a valid answer maybe e.g. using the provided query examples?

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

I found the answer here:

https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-2017

Important

If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error. SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.

It will effectively refactor the query into a series of joins before executing.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 25, 2019 at 9:32

Olivia

8141 gold badge14 silver badges27 bronze badges

1 Comment

Gordon Linoff Over a year ago

. . There is no "implicit" in this query at all. p.id specifically references the alias in the outer query, so this is an explicit correlated subquery.

Collectives™ on Stack Overflow

SQL where clause in nested query with column that doesnt exist in nested query

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related