on 2010 Dec 22 11:28 AM
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
Hi Robin......
Try this......
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]'
Group By t0.itemcode,t1.itemname, t1.frgnname, t1.u_prx_ptyp,t0.onorder, t0.onhand, t0.iscommited, t0.onorder, t0.whscode, t1.itemcode
ORDER BY t1.u_prx_ptyp, t1.itemcode asc
Regards,
Rahul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
92 | |
8 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.