Hello.
I have 1 purchase order that creates two jobs Master and Child for each stock code.

Each job uses different resources to create it's output.

What i'm trying to do with this sql sentence is to show the resources needed for each stock in the purchase order, but i don't know how to make the join of the Job2.

Thank you for your time.

select a.SalesOrder, a.MStockCode, a.MStockDes, a.MOrderQty, b.Job,
(select Job from WipMaster where JobMaestro = b.Job) as Job2,
c.MStockCode as Insumos,c.MDescription as NombreInsumo, c.MQtyIssued as CantidadInsumo,
d.MStockCode as Insumos2,d.MDescription as NombreInsumo2,d.MQtyIssued as CantidadInsumo2
from SorDetail as a
left join WipMaster as b on a.SalesOrder=b.SalesOrder and a.SalesOrderLine= b.SalesOrderLine
left join WipJobPost as c on b.Job = c.Job

left join WipJobPost as d on d.Job = Job2 <- HOW DO I TELL: USE THE JOB2 COLUMN

where a.SalesOrder = 155 and Left(c.MStockCode,1) <> 'C' and c.TrnType = 'R'