Help me with sql querry using c#

Question

2.23/5 (3 votes)

See more:

O God Why am getting the repeated values again and again pls some 1 help me

My Prob:

step1:
I used this query to get an unique value from an database and i got exact result

C#

select DISTINCT  fnlwgt from Dataset;

Step2:

I used this query to get entire records which must matches the unique value using join query here is my code

C#

da = new SqlDataAdapter("SELECT rule1.Age, rule1.workclass, rule1.fnlwgt, rule1.education, rule1.maritalstatus FROM rule1 INNER JOIN  probablity ON rule1.fnlwgt = probablity.fnlwgt", cn.con);

Am geting the result as

53 Self-emp-inc 100029 Doctorate Married-civ-spouse
53 Self-emp-inc 100029 Doctorate Married-civ-spouse
53 Self-emp-inc 100029 Doctorate Married-civ-spouse
53 Self-emp-inc 100029 Doctorate Married-civ-spouse
53 Self-emp-inc 100029 Doctorate Married-civ-spouse
53 Self-emp-inc 100029 Doctorate Married-civ-spouse
53 Self-emp-inc 100029 Doctorate Married-civ-spouse
53 Self-emp-inc 100029 Doctorate Married-civ-spouse
53 Self-emp-inc 100029 Doctorate Married-civ-spouse

28 Private 100219 HS-grad Never-married
28 Private 100219 HS-grad Never-married
28 Private 100219 HS-grad Never-married
28 Private 100219 HS-grad Never-married
28 Private 100219 HS-grad Never-married
28 Private 100219 HS-grad Never-married
28 Private 100219 HS-grad Never-married

55 Self-emp-not-inc 100569 HS-grad Separated
55 Self-emp-not-inc 100569 HS-grad Separated
55 Self-emp-not-inc 100569 HS-grad Separated
55 Self-emp-not-inc 100569 HS-grad Separated
55 Self-emp-not-inc 100569 HS-grad Separated
55 Self-emp-not-inc 100569 HS-grad Separated
55 Self-emp-not-inc 100569 HS-grad Separated
55 Self-emp-not-inc 100569 HS-grad Separated
55 Self-emp-not-inc 100569 HS-grad Separated
55 Self-emp-not-inc 100569 HS-grad Separated

As such i have nearly 31225 records

But the expected Result is

53 Self-emp-inc 100029 Doctorate Married-civ-spouse
28 Private 100219 HS-grad Never-married
55 Self-emp-not-inc 100569 HS-grad Separated

what should i do to get particular result pls some 1 help me

Posted 11-Jul-13 21:45pm

usha C

Add a Solution

Comments

berrymaria 12-Jul-13 3:49am

da = new SqlDataAdapter("SELECT DISTINCT rule1.Age, rule1.workclass, rule1.fnlwgt, rule1.education, rule1.maritalstatus FROM rule1 INNER JOIN probablity ON rule1.fnlwgt = probablity.fnlwgt", cn.con);

ArunRajendra 12-Jul-13 4:46am

Can you post the result what you are getting?

usha C 12-Jul-13 4:50am

c my ques i have mentioned what the result am getting........

Sushil Mate 12-Jul-13 4:47am

hey usha, please provide the both table schema. you don't have to use probablity table as you are not selecting from that table. remove inner join. I guess its not needed.

usha C 12-Jul-13 4:57am

Table 1 containts
17 Private 10th Never-married 163836
17 Private 10th Never-married 163836
18 Local-gov 12th Never-married 134935
18 Local-gov 12th Never-married 134935
18 Local-gov 12th Never-married 134935
19 Private 11th Never-married 236396
19 Private 11th Never-married 99246
19 Private 11th Never-married 99246

so n so and Table 2 is

100029
100219
100569
100579
100800
100875
100960
101345
101468
101562
101890
102076

its unique values

Sushil Mate 12-Jul-13 5:09am

why your table1 is having two entries of same record. where is the primary key?

usha C 12-Jul-13 5:19am

Actually above table does not contain any primary key column because of that same type of records exist.

Sushil Mate 12-Jul-13 5:20am

bad design. will not work.

usha C 12-Jul-13 5:33am

is it possible with LINQ querry sir... bt i dnt no hw to impliment tat query fr my code............pls do tel if u have any idea..am strugling wit dis from the morn

6 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pheonyx · Answer 1 · 2013-07-11T21:50:00

Solution 1

Try changing:

da = new SqlDataAdapter("SELECT rule1.Age, rule1.workclass, rule1.fnlwgt, rule1.education, rule1.maritalstatus FROM rule1 INNER JOIN  probablity ON rule1.fnlwgt = probablity.fnlwgt", cn.con);

to

da = new SqlDataAdapter("SELECT DISTINCT rule1.Age, rule1.workclass, rule1.fnlwgt, rule1.education, rule1.maritalstatus FROM rule1 INNER JOIN  probablity ON rule1.fnlwgt = probablity.fnlwgt", cn.con);

(Note the addition of the Distinct key word.)

Posted 11-Jul-13 21:50pm

Pheonyx

Comments

usha C 12-Jul-13 3:59am

No i tried with dis method also but am geting the same result oly am founding distinct keyword does not work for entire columns fr me wat to do...

Pheonyx 12-Jul-13 4:30am

Why do you need the join? you do not appear to be selecting anything from the second table?

Try removing the join completely.

usha C 12-Jul-13 4:55am

wat to do the next process if i remove the join query..

Pheonyx 12-Jul-13 5:01am

What next process??!! you have mentioned nothing about a follow up process. Based on what you have said and the code you posted the join is irrelevant. Remember we do not know what you are trying to achieve beyond what you type here. We do not have your system specification, we cannot read your mind nor see your screen.

usha C 12-Jul-13 5:09am

Hello sir i told tat am nt geting result with ur query if ter any further process is ter to find result i asked........... if nt keep quiet...

jagadeesh2530 · Answer 2 · 2013-07-11T23:25:00

Solution 5

SELECT DISTINCT rule1.Age, rule1.workclass, rule1.fnlwgt, rule1.education, rule1.maritalstatus FROM rule1 WHERE rule1.fnlwgt = (SELECT DISTINCT probablity.fnlwgt FROM probablity)

Posted 11-Jul-13 23:25pm

jagadeesh2530

Updated 11-Jul-13 23:35pm

v2

usha C · Answer 3 · 2013-07-12T21:46:00

C#

 private void button2_Click(object sender, EventArgs e)
        {
            da = new SqlDataAdapter("select * from rule1",cn.con);
            DataTable dtRemoveDuplicate = new DataTable();
                da.Fill(dtRemoveDuplicate);
                dtRemoveDuplicate = DeleteDuplicateFromDataTable(dtRemoveDuplicate);
                 dataGridView1.DataSource = dtRemoveDuplicate;
                  //dataGridView1.DataBind();
        }

private DataTable DeleteDuplicateFromDataTable(DataTable dtRemoveDuplicate)
        {
           
            DataView dView = new DataView(dtRemoveDuplicate);
            string[] arrColumns = { "Age", "workclass", "fnlwgt", "education", "maritalstatus" };
            dtRemoveDuplicate = dView.ToTable(true, arrColumns);
            return dtRemoveDuplicate;
            throw new NotImplementedException();
        }

berrymaria · Answer 4 · 2013-07-11T21:52:00

Solution 2

da = new SqlDataAdapter("

SQL

SELECT DISTINCT rule1.Age, rule1.workclass, rule1.fnlwgt, rule1.education, rule1.maritalstatus FROM rule1 INNER JOIN  probablity ON rule1.fnlwgt = probablity.fnlwgt

", cn.con);

Posted 11-Jul-13 21:52pm

berrymaria

Comments

usha C 12-Jul-13 3:59am

No i tried with dis method also but am geting the same result oly am founding distinct keyword does not work for entire columns fr me wat to do...

berrymaria 12-Jul-13 4:06am

Maybe there's something wrong with your linking of rule1.fnlwgt and probability.fnlwgt, or there is still another field that you have to link for you to get the distinct data.

usha C 12-Jul-13 4:07am

Am struggling with dis code from morning pls do me a favour or give some other method to get my output

berrymaria 12-Jul-13 4:41am

Have you tried SELECT DISTINCT {...} FROM tableName LEFT JOIN ?

usha C 12-Jul-13 4:45am

Yes tried is ter any other methos to remove duplicate records

berrymaria 12-Jul-13 4:47am

Can you just remove your inner join? You're not using it in the select statement.

usha C 12-Jul-13 4:53am

Ya tried bt no use

berrymaria 12-Jul-13 4:57am

Remove the inner join, then Select Distinct? Have you already check the table design? (There are might also data that made a difference to each other data, like the Datetime that's why you can't get it distinct.)

Priyanka7777 12-Jul-13 4:53am

what chimcham said is right.There is still another field that you have to link for you to get the distinct data.Check for more linking between the two tables.

usha C 12-Jul-13 5:03am

ya done with linking to second table bt no use c dis code
da = new SqlDataAdapter("SELECT DISTINCT rule1.Age, rule1.workclass, rule1.fnlwgt, rule1.education, rule1.maritalstatus, probablity.fnlwgt FROM rule1 INNER JOIN probablity ON rule1.fnlwgt = probablity.fnlwgt", cn.con);

Priyanka7777 12-Jul-13 5:12am

This is the only field that you have added in your query:
rule1.fnlwgt = probablity.fnlwgt.

Check for similar links that you can make between rule1 table and probablity.

jagadeesh2530 · Answer 5 · 2013-07-11T21:53:00

Solution 3

use LEFT JOIN instead using INNER JOIN

Posted 11-Jul-13 21:53pm

jagadeesh2530

Comments

usha C 12-Jul-13 4:02am

no sir its not working

Sushil Mate · Answer 6 · 2013-07-11T22:55:00

Solution 4

C#

da = new SqlDataAdapter(SELECT DISTINCT *
FROM (SELECT rule1.Age, rule1.workclass, rule1.fnlwgt, rule1.education, rule1.maritalstatus FROM rule1 INNER JOIN  probablity ON rule1.fnlwgt = probablity.fnlwgt) As newTable, cn.con);

Posted 11-Jul-13 22:55pm

Sushil Mate

Comments

usha C 12-Jul-13 5:07am

Yep same issues fallowing wit ur code too....

Help me with sql querry using c#

6 solutions

Solution 1

Solution 5

Solution 6

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Help me with sql querry using c#

6 solutions

Solution 1

Solution 5

Solution 6

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us