Query a subquery

Transact-SQL
1

I've never had to do this before, but is it possible to query a subquery? I want to query from the "oh" from the "agt" subquery

select oh.opptyHeaderID,oh.SalesId, agt.AgentTeamAsnmtSeq, agt.AgentId, agt.TeamId
from
(SELECT *
FROM dbo.spOpptyHeader
WHERE OpptyHeaderId IN (
'1072388', '1072525', '1074973', ) oh
INNER join
(select atm.AgentTeamAsnmtSeq, atm.AgentId, atm.TeamId, sales_id
from spAgentTeamAsnmt atm,spAgent
WHERE AgentId in
(select * from spAgent WHERE SalesID IN (oh) )
) agt
on oh.salesid = agt.sales_id

2

Hard to tell exactly what you want to do, but you can use a CROSS APPLY to allow you to reference columns from the first derived table:

select oh.opptyHeaderID,oh.SalesId, agt.AgentTeamAsnmtSeq, agt.AgentId, agt.TeamId
from 
(SELECT * 
FROM dbo.spOpptyHeader
WHERE OpptyHeaderId IN (
'1072388', '1072525', '1074973' )
) oh
CROSS APPLY
(select atm.AgentTeamAsnmtSeq, atm.AgentId, atm.TeamId, sales_id
from spAgentTeamAsnmt atm,spAgent
WHERE AgentId in 
(select AgentID from spAgent WHERE SalesID = oh.SalesID)
) agt
3

I'm guessing too, but would a CTE give you access to the "subquery" that you need to columns from?