Duplicate rows in result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicate rows in result

Author

Topic

Plyft
Starting Member

8 Posts

Posted - 2008-02-06 : 03:06:14

Hi!

My problem is as u can see below that i get out duplicate rows and i cant figure out how to fix it.
The result looks like this:

INVOICE_ID DATE_INVOICED PRODUCT_NAME TEXT_ROW_INFO CATEGORY SERIALNUMBER PRICE_1
4856298 2007-11-01 MOTORPART_10 CAR 5LZ419BG 4795.00
4856298 2007-11-01 OTHER_ITEM_17 OTHER 5LZ419BG 716.00
4856298 2007-11-01 MOTORPART_10 CAR SCNU7352DFP 4795.00
4856298 2007-11-01 OTHER_ITEM_17 OTHER SCNU7352DFP 716.00

As you can see the products get repeted whit not only there own uniq serialnumber but whit every products serialnumber and price ect.

The result ofcourse should be
INVOICE_ID DATE_INVOICED PRODUCT_NAME TEXT_ROW_INFO CATEGORY SERIALNUMBER PRICE_1
4856298 2007-11-01 OTHER_ITEM_17 OTHER 5LZ419BG 716.00
4856298 2007-11-01 MOTORPART_10 CAR SCNU7352DFP 4795.00

The query looks like this.

SELECT DISTINCT I.INVOICE_ID
,I.DATE_INVOICED
,IR.PRODUCT_NAME
,IR.TEXT_ROW_INFO
,[CATEGORY] =
CASE
WHEN INV.GROUP_ID IN ('318','319','320','321','322','5164','5380') THEN 'CAR'
WHEN INV.GROUP_ID IN ('361','362','363','364','366','5055','5381') THEN 'BIKE'
WHEN INV.GROUP_ID IN ('779','5519','5520')THEN 'PLANE'
ELSE 'OTHER'
END
,INS.SERIALNUMBER
,IR.PRICE_1
FROM INVOICE AS I
JOIN INVOICE_ROW AS IR ON I.INVOICE_SEQ_ID = IR.INVOICE_SEQ_ID
JOIN INVENTORY AS INV ON IR.PRODUCT_ID = INV.PRODUCT_ID
JOIN INVOICE_SERIALNUMBER AS INS ON I.INVOICE_SEQ_ID = INS.INVOICE_SEQ_ID WHERE I.DATE_INVOICED >= '20071101'and i.date_invoiced < '20071102'
AND IR.INVOICE_SEQ_ID IN
(SELECT INV.INVOICE_SEQ_ID FROM INVOICE INV
JOIN INVOICE_ROW INVR ON (INV.INVOICE_SEQ_ID = INVR.INVOICE_SEQ_ID)
JOIN INVENTORY IVT ON (INVR.PRODUCT_ID = IVT.PRODUCT_ID)
JOIN INVENTORY_GROUPS ING ON(IVT.GROUP_ID = ING.ID)
WHERE ING.ID IN ('5483'))

ORDER BY I.INVOICE_ID ASC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 03:15:26

can you post structure of your tables with some sample data?

Plyft
Starting Member

8 Posts

Posted - 2008-02-06 : 04:37:57

Here are the key structure of the tables included

INVOICE
invoice_seq_id (PK)
Customer_id (FK)
...
INVOICE_ROW
invoice_row_id (PK)
invoice_seq_id (FK)
product_id (FK)
....
INVENTORY
product_id (PK)
INVOICE_SERIALNUMBER
serialnumber (PK)
product_id (PK)
invoice_seq_id (PK)
....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources