SQL Selects combine - Second select to be in Where clause

Question

I have 2 selects. The first one:

select purs.t_orno as purchased
from ttisfc001201 sls
inner join twhinr110201 sfc on sfc.t_orno = sls.t_pdno
inner join twhltc100201 purs on purs.t_clot=sfc.t_clot
left join twhltc220201 items on items.t_clot = sfc.t_clot
left join twhltc210201 cert_num on cert_num.t_item = items.t_item
left join twhltc200201 cert on cert.t_ltft = cert_num.t_ltft
where sls.t_cprj = 'SLS004336' and purs.t_orno like N'PUR%'
and sfc.t_koor = 1 and sfc.t_kost = 5

Is giving me these results:

PUR007833
PUR008544
PUR008698
PUR008963
PUR009048
PUR009304
PUR009611
PUR009912
PUR009913
PUR010006
PUR010110
PUR010400
PUR010465
PUR010539
PUR010664

So basically these are results I must use in the second select in where clause. A field from table in second select must be equal to one of them. To understand me better it should look like this:

select distinct fac.t_isup
from ttfacp200201  fac
inner join  ttfacp250201 mid on mid.t_ityp = fac.t_ttyp and mid.t_idoc=fac.t_ninv
where mid.t_orno ='PUR010400' or mid.t_orno='PUR009912'or mid.t_orno='PUR009913'or mid.t_orno='PUR010465'or mid.t_orno='PUR008544'or mid.t_orno='PUR008963'or mid.t_orno='PUR009048'or mid.t_orno='PUR010110'or mid.t_orno='PUR007833'or mid.t_orno='PUR009304'or mid.t_orno='PUR009611'or mid.t_orno='PUR010664'or mid.t_orno='PUR010006'or mid.t_orno='PUR010539'or mid.t_orno='PUR008698'or mid.t_orno='PUR010667'

All these ORs are results from the first select. How I can combine them (the first select to go in second select where clause) so I can get results at once?

Answer 1

You can use the IN clause for your second query

select distinct fac.t_isup
from ttfacp200201  fac
inner join  ttfacp250201 mid on mid.t_ityp = fac.t_ttyp and mid.t_idoc=fac.t_ninv
where mid.t_orno IN (
    select purs.t_orno 
    from ttisfc001201 sls
    inner join twhinr110201 sfc on sfc.t_orno = sls.t_pdno
    inner join twhltc100201 purs on purs.t_clot=sfc.t_clot
    left join twhltc220201 items on items.t_clot = sfc.t_clot
    left join twhltc210201 cert_num on cert_num.t_item = items.t_item
    left join twhltc200201 cert on cert.t_ltft = cert_num.t_ltft
    where sls.t_cprj = 'SLS004336' and purs.t_orno like N'PUR%'
    and sfc.t_koor = 1 and sfc.t_kost = 5
)