Skip to Content
0
Former Member
Dec 22, 2010 at 11:28 AM

Adding POR1 to OITM, OITB

224 Views

I'm on a big learning curve.........can anyone please explain why when I add the 3rd inner join it reports multiple lines against each Item No.?

I need to clearly understand how and where to make various joins.

SELECT t0.itemcode,t1.itemname, t1.frgnname, t1.u_prx_ptyp, cast(t0.onhand as int) as 'Stock', cast (t0.iscommited as int) as 'Sales Orders', (cast(t0.onhand as int) - cast(t0.iscommited as int)) as 'Free Stock', cast(t0.onorder as int) as 'Purchase Orders', (cast(t0.onhand as int) - cast(t0.iscommited as int) + cast(t0.onorder as int)) as 'Total'

FROM

oitw t0

inner join oitm t1 on t0.itemcode = t1.itemcode

inner join oitb t2 on t1.itmsgrpcod = t2.itmsgrpcod

inner join por1 t3 on t0.itemcode = t3.itemcode

WHERE

t0.whscode = 'ingh' and t1.u_prx_ptyp = '[%0]'

ORDER BY t1.u_prx_ptyp, t1.itemcode asc

Many thanks,

Robin