Record repeat four times when execute query in sql server 2005

Question

hi all

i have problem when i execute the query the record being repeated four times whereas in my database there is only have one record. I think the problem is arising due to relationship because table Head relate with table ItemsChart, table Requsition relate with RequisitionDetail and table Issued relate with table IssuedDetail. all the table has one records as opening, receiving and issuing one each items but when i execute the query the resulted query will be showing three records of each items like below picture.

i don't know where is problem my query is as under

SELECT     dbo.IssuedDetail.ItemID, dbo.IssuedDetail.ItemName, dbo.ItemsChart.openingqty AS OpeningQty, dbo.RequisitionDetail.Accept AS QtyIn,
                      dbo.IssuedDetail.IssuedQty AS QtyOut, dbo.IssuedDetail.Price, ISNULL(SUM(dbo.ItemsChart.openingqty), 0) + ISNULL(SUM(dbo.RequisitionDetail.Accept), 0)
                      - ISNULL(SUM(dbo.IssuedDetail.IssuedQty), 0) AS QtyBalance
FROM         dbo.RequisitionDetail INNER JOIN
                      dbo.Requistion ON dbo.RequisitionDetail.ReceivingID = dbo.Requistion.ReceivingID CROSS JOIN
                      dbo.IssuedDetail INNER JOIN
                      dbo.Issued ON dbo.IssuedDetail.IssuedID = dbo.Issued.IssuedID CROSS JOIN
                      dbo.Head INNER JOIN
                      dbo.ItemsChart ON dbo.Head.HeadID = dbo.ItemsChart.HeadID
GROUP BY dbo.IssuedDetail.ItemID, dbo.IssuedDetail.ItemName, dbo.ItemsChart.openingqty, dbo.RequisitionDetail.Accept, dbo.IssuedDetail.IssuedQty, dbo.IssuedDetail.Price

Answer 1

because in other columns the values are different and based on those values it duplicated first two columns value..there is no any issue with query.

---

Please vote if you find this posting was helpful or Mark it as answered.

Answer 2

when i sum of OpeninQty,QtyIn & QtyOut values than there is showing four times sum of single value of all the quantity columns and the query will be showing in two rows. I just want to sum of itemID wise

please help me

Answer 3

try this : you can get max value for each fields accept first two fields :

SELECT     dbo.IssuedDetail.ItemID, dbo.IssuedDetail.ItemName, MAX(dbo.ItemsChart.openingqty) AS OpeningQty, MAX(dbo.RequisitionDetail.Accept) AS QtyIn, 
                      MAX(dbo.IssuedDetail.IssuedQty) AS QtyOut, MAX(dbo.IssuedDetail.Price), ISNULL(SUM(dbo.ItemsChart.openingqty), 0) + ISNULL(SUM(dbo.RequisitionDetail.Accept), 0) 
                      - ISNULL(SUM(dbo.IssuedDetail.IssuedQty), 0) AS QtyBalance
FROM         dbo.RequisitionDetail INNER JOIN
                      dbo.Requistion ON dbo.RequisitionDetail.ReceivingID = dbo.Requistion.ReceivingID CROSS JOIN
                      dbo.IssuedDetail INNER JOIN
                      dbo.Issued ON dbo.IssuedDetail.IssuedID = dbo.Issued.IssuedID CROSS JOIN
                      dbo.Head INNER JOIN
                      dbo.ItemsChart ON dbo.Head.HeadID = dbo.ItemsChart.HeadID
GROUP BY dbo.IssuedDetail.ItemID, dbo.IssuedDetail.ItemName

---

Please vote if you find this posting was helpful or Mark it as answered.

Answer 4

Hi

You have "duplicates" because this is not unique dbo.RequisitionDetail.Accept AS QtyIn.

You need to change your logic for this column. If you take it from select and group by then you will get "unique row". If you need the column than you can take it out from group by and use SUM or MAX/MIN in select that will give you unique row. If there is special logic around dbo.RequisitionDetail.Accept then you will have to implement it or share it with us.

I hope that helps

Emil

---

Katie & Emil Microsoft SQL BI related blog & tutorials SQL Tutorial | SSRS Tutorial | SSIS Tutorial

Answer 5

hi

i have really troubled from last one week but I was unable to resolve my issue please give me your email address i send you my database for in order to my problem has been resolved

Answer 6

Why not using ROW_NUMBER along with PARTITION clause to remove duplicates 

http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/