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?

You can try passing tipoId.id from query A to Query B via the 'IN' Operator. — Hackinet, Feb 28 '19 at 22:21

GMB · Accepted Answer · 2019-03-01T00:28:10.107

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

edited Mar 01 '19 at 00:28

answered Feb 28 '19 at 23:00

GMB

216,147
25
84
135

That souds like a good solution, but there is one thing that it is not considered. In Query A they are ordered by op.numOpciones and seems like it is not ordering like it should. – Henry B Mar 01 '19 at 00:04
@HenryB : ok I adapted the `ORDER BY` clause (and accordingly the `GROUP BY` clause). Please let me know if this is working for your now ? – GMB Mar 01 '19 at 00:29

Gordon Linoff · Answer 2 · 2019-03-01T00:30:23.893

0

Is this what you want?

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

edited Mar 01 '19 at 00:30

answered Feb 28 '19 at 22:56

Gordon Linoff

1,242,037
58
646
786

That souds like a good solution, but there is one thing that it is not considered. In Query A they are ordered by op.numOpciones and seems like it is not ordering like it should. – Henry B Mar 01 '19 at 00:04

Find a Query inside another Query in SQL Server

2 Answers2