Find a Query inside another Query in SQL Server

Question

So I have 2 different queries that work the way they are intended to. Query A:

select tipo.descripcion as tipoId, tipo.id
from Empleados emp 
inner join dbo.Opciones op ON op.empleadoId = emp.id 
inner join dbo.TipoEquipo tipo ON tipo.id = op.tipoEquipoId 
where username='user' order by op.numOpcion

Result A:

tipoId      id
EquipmentD  3
EquipmentC  10
EquipmentB  11
EquipmentA  13

Query B:

select 
    tip.id, 
    tip.descripcion as Descripcion, 
    sum(case when eq.asignado='No' Then 1 Else 0 END) as conteoNo
from Equipos eq 
left outer join TipoEquipo tip on tip.id=eq.tipoId
group by tip.descripcion, tip.id

ResultB:

id  Descripcion conteoNo
1   EquipmentY  3
2   EquipmentX  64
3   EquipmentD  31
4   EquipmentF  0
5   EquipmentH  1
7   EquipmentO  1
8   EquipmentT  5
9   EquipmentG  8
10  EquipmentC  7
11  EquipmentB  2
13  EquipmentA  2

So I want to cross the results, because they have a unique identifiers that are shared by tables, So I want to join them, maybe using a Inner join?

Something like A.id with B.id and display something like id|tipoId|conteo (but just using the values found on Query A. I was thinking something like a select inside another select. Something like Results A + conteoNo.

select tipo.descripcion as tipoId, tipo.id
from Empleados emp inner join (
    select 
        tip.id, 
        tip.descripcion as Descripcion, 
        sum(case when eq.asignado='No' Then 1 Else 0 END) as conteoNo
    from Equipos eq 
    left outer join TipoEquipo tip on tip.id=eq.tipoId
    group by tip.descripcion, tip.id
) x on tipo.id = x.id
inner join dbo.Opciones op ON op.empleadoId = emp.id 
inner join dbo.TipoEquipo tipo ON tipo.id = op.tipoEquipoId 
where username='landerosj' order by op.numOpcion

Don't know if this is even possible, or should I just go with an Stored Procedure?

Answer 1

I think that it might be possible to just bring in the Equipos table into the first query with a LEFT JOIN, and then turn on aggregation. This would produce a much simpler query than when joining the results of the two queries :

Try:

select 
    tipo.id,
    tipo.descripcion as tipoId, 
    op.numOpcion,
    sum(case when eq.asignado='No' Then 1 Else 0 END) as conteoNo
from  dbo.Empleados emp 
inner join dbo.Opciones op     on op.empleadoId = emp.id 
inner join dbo.TipoEquipo tipo on tipo.id = op.tipoEquipoId
left  join dbo.Equipos eq       on tipo.id=eq.tipoId
where username='user' 
group by tipo.id, tipo.descripcion, op.numOpcion
order by op.numOpcion