SQL - pasar el valor de un select a una variable

Buenas tardes.
Es mi primer mensaje en este foro y me temo que repetire con cierta frecuencia.
Desde hace tiempo me ha gustado el mundillo de la programacion y he de decir que no se me da mal del todo; eso si, sin llegar a niveles de experto ni mucho menos.
A día de hoy se programar varias marcas de automatas, en C++, lenguaje de CNC, varias marcas de robots... y por razones de trabajo empiezo a necesitar tener nociones de SQL.
Mientras llega el curso que he pedido estoy en "modo autodidacta" con la ayuda puntual de un par de informaticos de la empresa.

El caso es que se hace alguna cosa basica, como algun select o algun inner join. Hoy me he encontrado en la necesidad de almacenar el resultado de un SELECT en una variable para luego utilizar dicha variable en otro SELECT.

Googleando he conseguido hacerlo y funciona pero tengo un problema y es que el resultado del SELECT que almaceno en una variable puede ser unico y todo es perfecto o puede ser multiple y solo consigo el ultimo dato; esto no me sirve. Necesito el dato de todas las filas que me devuelve el SELECT para luego usarlo en mi segundo SELECT

Aqui el codigo...

declare @orden nchar(10)
declare @lote nchar(10)
SELECT @orden = '702476' --S1

SELECT @lote = Lote_externo FROM Maldivas_Oran.Logistica.Articulos_lotes Lot where Lot.Orden_trabajo = CAST(@orden as INT) --S2

SELECT batchidentifier AS LOTE, manufacturingReference AS REFERENCIA, initialQuantity AS Q_LOTE FROM Batch where batchIdentifier = @lote --S3


Un resultado del SELECT S2 podria ser por ejemplo
BNE001
BNE002
BNE003
BNE004
Pero solo obtengo BNE004 y necesito los 4 datos (y ejecutar el SELECT S3 con los 4 resultados)


Muchas gracias a todos!!

Puedes hasce un subquery dentro del primero.Seria algo como

SELECT batchidentifier AS LOTE, manufacturingReference AS REFERENCIA, initialQuantity AS Q_LOTE
FROM Batch
WHERE batchIdentifier IN (
SELECT Lote_externo
FROM Maldivas_Oran.Logistica.Articulos_lotes Lot
WHERE Lot.Orden_trabajo = CAST(@orden as INT))

Como dije... no tengo mucha idea de esto.
Entiendo que es un SELECT dentro de otro. Algo asi como un SELECT recursivo (no se si esta puede ser la palabra correcta...)
Desde mi desconocimiento del tema he de decir que tiene mucho sentido y buena pinta.
Mañana lo pruebo. Estoy consultando esto desde casa porque quiero evitar el uso de foros en el trabajo.
Una vez mas... muchas gracias!!

No creo que la propuesta de IN sea la mas adecuada, ¿Que tal un EXISTS o NOT EXISTS?

Podrias ponerme un ejemplo de codigo con EXISTS sobre el ejemplo que he puesto??
Sin mas datos me quedo un poco perdido...

"As is"

Hola Aqui te dejo un ejemplo, espero te sirva