July 14, 2014 at 12:05 pm
please could someone help me with the below query. have tried joining several tables and the result displays duplicate rows of virtually every line/row. i have tried using distinct but this didn't help. I know it could because there's several columns from some of the tables named the same but being a newbie I don't know how to deal with this. Please could someone advise or provide an example? That would be great. thanks
select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,
goodsreceiptitems.createddate,
stockbatches.warehouseid,
stockbatches.locationid,
stockbatches.quantity,
stockbatches.status,
stockbatches.batchnumber,
stockbatches.batchid,
stockbatches.origin,
stockbatches.unitvalue,
goodsreceiptitems.orderid from stockbatches
inner join allpartmaster on stockbatches.partid = allpartmaster.partnum
inner join goodsreceiptbatches on stockbatches.batchnumber = goodsreceiptbatches.stockbatchid
inner join goodsreceiptitems on goodsreceiptbatches.goodsreceipt = goodsreceiptitems.shipmentid
inner join purchaseorders on goodsreceiptitems.orderid = purchaseorders.id
inner join suppliers on purchaseorders.traderid = suppliers.id
where allpartmaster.prodgroup = 'RMTAILS' and stockbatches.quantity >0
July 14, 2014 at 12:40 pm
You could start with this extended query, and find/analyse the rows having count(*) > 1.
select
purchaseorders.traderid,
suppliers.name,
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,
goodsreceiptitems.createddate,
stockbatches.warehouseid,
stockbatches.locationid,
stockbatches.quantity,
stockbatches.status,
stockbatches.batchnumber,
stockbatches.batchid,
stockbatches.origin,
stockbatches.unitvalue,
goodsreceiptitems.orderid,
COUNT(*)
from stockbatches
inner join allpartmaster on stockbatches.partid = allpartmaster.partnum
inner join goodsreceiptbatches on stockbatches.batchnumber = goodsreceiptbatches.stockbatchid
inner join goodsreceiptitems on goodsreceiptbatches.goodsreceipt = goodsreceiptitems.shipmentid
inner join purchaseorders on goodsreceiptitems.orderid = purchaseorders.id
inner join suppliers on purchaseorders.traderid = suppliers.id
where allpartmaster.prodgroup = 'RMTAILS' and stockbatches.quantity >0
GROUP BY
purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,
goodsreceiptitems.createddate,
stockbatches.warehouseid,
stockbatches.locationid,
stockbatches.quantity,
stockbatches.status,
stockbatches.batchnumber,
stockbatches.batchid,
stockbatches.origin,
stockbatches.unitvalue,
goodsreceiptitems.orderid
Igor Micev,My blog: www.igormicev.com
July 14, 2014 at 1:23 pm
Thanks alot, I'll look into that. Really appreciate your help.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply