How to avoid duplicate data during inner join

Pavithra L

10y1.3k.NET

Hai ,

In my project am using C# windows application. Here am I using Inner join for connect two datatable . I want to avoid the duplicate data during INNER JOIN from one table.

For an Example:

Job No	Order Qty	Job No	Knitt Qty	Machine
1	1000	1	500	26
1	1000	1	500	26

am using below code for this table:

Here I want to avoid the 2nd row entry of Job No and Order Qty only . How can I do this. Please anybody help this.

da = new SqlDataAdapter("select j.Group_Name,j.Due_Amt,p.Receipt_No,p.Customer_Name,p.Paid_Amount from Chit j INNER JOIN Chit_Collection p on j.Group_Name=p.Group_Name GROUP BY j.Group_Name", con1);

dt = new DataTable();

da.Fill(dt);

foreach (DataRow item in dt.Rows)

{

int n = dataGridView1.Rows.Add();

dataGridView1.Rows[n].Cells[0].Value = item["Group_Name"].ToString();

dataGridView1.Rows[n].Cells[1].Value = item["Due_Amt"].ToString();

dataGridView1.Rows[n].Cells[2].Value = item["Receipt_No"].ToString();

dataGridView1.Rows[n].Cells[3].Value = item["Customer_Name"].ToString();

dataGridView1.Rows[n].Cells[4].Value = item["Paid_Amount"].ToString();

}

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gomathi Raguprasad10y ago
Hi,
I think, Its not possible to make group name and Due values to null or delete with your requirement.Because the customer and group name mapping will get affect.
select distinct j.GroupName,j.Due from Chit j INNER JOIN Chit_Collection p on j.GroupName=p.GroupName

SELECT ReceiptNo,Customer,Paid FROM Chit j INNER JOIN Chit_Collection p on j.GroupName=p.GroupName
My solution is , you can go for data table(C#) manipulation once you get the table result from sql.That's the easy method.

Thanks
Gomathi
Ranjit Powar10y ago
You have to show paid amount of all customers in group
Pavithra L10y ago
Hai Raja,

Is there any way to avoid this repetition data.

Thanks for your suggestion.
Raja T10y ago
Hi Pavithra,

In Chit table you are having 1 record for groupname 100. but in Chit_Collection table you are having 3 records. So when you inner join both tables you will get 3 records with different customer name.
Pavithra L10y ago
Hai Raja,

In Chit table values are
Group Name Months Due Start Date End Date
100 11 100 09-12-2015 09-11-2016
200 11 200 09-12-2015 09-11-2016
300 11 300 09-12-2015 09-11-2016
400 11 400 09-12-2015 09-11-2016

Chit_Collection table values are

Receipt No Date Customer Group Name Paid
1 12-12-2015 Pavi 100 100
2 12-12-2015 Sri 100 100
3 12-12-2015 Mani 100 100
4 12-12-2015 Pavi 200 200

Like this .
I want to combine these two tables and filter the data based on group name and avoid the repetition data . Please suggest any idea to over come this
Raja T10y ago
Hello Pavithra, Below table customer name is different so showing duplicate.

Group Name Due Group Name Customer Paid
100 100 100 Pavi 100
100 100 100 Sri 100
100 100 100 Mani 100
100 100 100 Raji 100

If you possible share you individual records each tables Chit and Chit_Collection.
Pavithra L10y ago
Hai ,

Are you clear about that or not. If you not understand means please tell me. I will explain again .

Give me any other suggestion to avoid this .

Thanks for your reply.
Pavithra L10y ago
Hai,

select j.Group_Name,j.Due_Amt,p.Receipt_No,p.Customer_Name,p.Paid_Amount from Chit j INNER JOIN Chit_Collection p on j.Group_Name=p.Group_Name GROUP BY j.Group_Name

am not using this line in my execution i wrongly copied this one.

During this query excution, my result is below

Group Name Due Group Name Customer Paid
100 100 100 Pavi 100
100 100 100 Sri 100
100 100 100 Mani 100
100 100 100 Raji 100

This is the exact result , I want to avoid this below marked details.
Group Name Due Group Name Customer Paid
100 100 100 Pavi 100
100 100 100 Sri 100
100 100 100 Mani 100
100 100 100 Raji 100

How to avoid this.
Manas Mohapatra10y ago
When you are executing below query what is the result you are getting:

select j.Group_Name,j.Due_Amt,p.Receipt_No,p.Customer_Name,p.Paid_Amount from Chit j INNER JOIN Chit_Collection p on j.Group_Name=p.Group_Name GROUP BY j.Group_Name

The above was taken from from your question.
Pavithra L10y ago
Hai ,

I tried that it can't be work.. Is this any other idea to avoid this.

Please give me any suggestion to avoid this .

Thank you.
Manas Mohapatra10y ago
Use "distinct" concept to avoid duplicate records.

Group Name	Months	Due	Start Date	End Date
100	11	100	09-12-2015	09-11-2016
200	11	200	09-12-2015	09-11-2016
300	11	300	09-12-2015	09-11-2016
400	11	400	09-12-2015	09-11-2016

Receipt No	Date	Customer	Group Name	Paid
1	12-12-2015	Pavi	100	100
2	12-12-2015	Sri	100	100
3	12-12-2015	Mani	100	100
4	12-12-2015	Pavi	200	200