Remove duplicate rows in multiple join query

Eng Hasan Abbas

hey , i have a query that collects data from 3 tables using one inner join and left join but the problem that there are many duplicate rows <

SELECT tbl_salles_bill.sb_id, ( tbl_salles_bill.sb_price_after_alll_dicount - tbl_salles_bill.sb_paid_money) as Dept,
tbl_customer.cust_fName + ' ' + tbl_customer.cust_mName + ' ' + tbl_customer.cust_lName AS CustomerName, tbl_customer.cust_city, tbl_receiving_money_receipt.rmr_id,
tbl_receiving_money_receipt.rmr_value AS Creditor
FROM tbl_salles_bill INNER JOIN
tbl_customer ON tbl_salles_bill.cust_id = tbl_customer.cust_id and tbl_salles_bill.sb_status='Paid'
left JOIN
tbl_receiving_money_receipt ON tbl_customer.cust_id = tbl_receiving_money_receipt.cust_id and tbl_receiving_money_receipt.rmr_IsPaidForWhat = 'Paid without recieving goods'
WHERE (tbl_customer.cust_id = 6)

thanks for your time. >

Mycroft Holmes

Underscore make that unreadable bleh... What I do is a 2 query pass, the inside query uses ROW_NUMBER() and PARTITION OVER the key fields (those that make up the unique record) I then delete records with a row number > 1 Here is a snippet I keep around for deduping

DECLARE @Tbl TABLE (IDField INT, RowNo INT)

--insert the primary key (identity field)
INSERT @Tbl
SELECT lnkStrategyNodeID IDfield,

--partition over the unique constraints - order by is required and logically you should use the lowest ID field
ROW_NUMBER() OVER(PARTITION BY StrategyID,NodeID ORDER BY lnkStrategyNodeID) Rw
FROM lnkStrategyNode

--delete anything that is > 1
--SELECT *
DELETE
FROM lnkStrategyNode
WHERE lnkStrategyNodeID IN (SELECT IDField FROM @Tbl WHERE RowNo > 1)

Never underestimate the power of human stupidity RAH

Chris Quinn

Use the DISTINCT keyword

SELECT DISTINCT tbl_salles_bill.sb_id, ( tbl_salles_bill.sb_price_after_alll_dicount - tbl_salles_bill.sb_paid_money) as Dept,
tbl_customer.cust_fName + ' ' + tbl_customer.cust_mName + ' ' + tbl_customer.cust_lName AS CustomerName, tbl_customer.cust_city, tbl_receiving_money_receipt.rmr_id,
tbl_receiving_money_receipt.rmr_value AS Creditor
FROM tbl_salles_bill INNER JOIN
tbl_customer ON tbl_salles_bill.cust_id = tbl_customer.cust_id and tbl_salles_bill.sb_status='Paid'
left JOIN
tbl_receiving_money_receipt ON tbl_customer.cust_id = tbl_receiving_money_receipt.cust_id and tbl_receiving_money_receipt.rmr_IsPaidForWhat = 'Paid without recieving goods'
WHERE (tbl_customer.cust_id = 6)

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

Member 10714909

plz visit here this tutorial may be helpful to you. http://www.javatpoint.com/sql-delete-duplicate-rows[^]