how to slove data duplication when using inner join?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

hi i am new to sql sevre 2008 Actuvally i have 3 table from each table i want to display some feild like table

studentregistration:

stdRid  stdRClass ... so on
1         A
2         B
3         C
4         A
5         B

studentDetails:

stdDid   stdDname stdDMarks ..so on

  1        xx        30
  2        yy        50
  3        zz        43
  4        uu        21
  5        ll        36

studentAdress

stdAid    dist        Aera........son on

 1        khamm     busstand
 2        RR      lingampally
 3        wrgl     temple

i have writen qury like this

SQL

SELECT SR.stdRClass , SD.stdDname , SD.stdDMarks , SA.dist, SA.Aera
FROM studentregistration SR
INNER JOIN studentDetails SD ON SR.stdRid = SD.stdDid  
INNER JOIN studentAdress SA ON  SD.stdDid = SA.stdAid
where stdRid='1' and
(SR.stdRclass in (select distinct SR.StdRclass from studentregistration where stdRid='1'))and
(SD.stdDname in (seelct distinct SD.stdDname from Studentdetails where stdDid='1'))and
(SD.stdDmarks in (selcet distinct SD.StdDMarks from studentDetails where stdDid='1'))and
(SA.dist in (select distinct SA.dist from StudentAddress where stdAid='1'))and
(SA.Aera in (select distinct SA.Aera from studentAdress where stdAid='1')) 
group by SR.stdRClass ,SD.stdDname ,SD.stdDMarks ,SA.dist, SA.Aera;

but by doing like all dupication is coming plz ingore this and tell me plz help me

plz consider below this tables instead of that sry i was not mention . thanks adavance help me plz
StdententDetails:
stdDid StdDname stdDmarks
1 xx 50
1 xx 30
1 xx 40
2 yy 31
2 yy 32
2 yy 43
3 zz 7 ..so on
studentAdress
StdAid dist Aera
1 kham busstand
1 khamm temple
1 kham Hospital
2 RR lingampally
2 RR chandanagar
3 wrgl temple
3 wrgl chorastha
i want to like this
o/p:note;this is only for stdid=1

StdRclassA StdDname StdMarks dist Aera
A xx 50 kham busstand
A XX 30 kham temple
A xx 40 khamm Hospital
help me
but its coming like this
StdRclassA StdDname StdMarks dist Aera
A XX 50 kham Bustand
A XX 50 kham temple
A XX 50 kham Hospital
A XX 30 kham bustand
A xx 30 kham temple
A xx 30 kham hospital
A XX 40 kham bustand
A xx 40 kham temple
A xx 40 kham hospital
like this coming help me

Posted 8-Mar-13 0:32am

Member 9846414

Updated 8-Mar-13 19:38pm

v6

Add a Solution

Comments

gvprabu 8-Mar-13 6:41am

In any of your Table have multiple values for single Student, SO u are getting Duplicate data.
So tel the exact requirement.... how u need to display...?

Member 9846414 8-Mar-13 8:12am

ya in table studentdetatils
stdDid stdDname StdDmarks
1 xx 30
1 xx 30
1 xx 30
2 yy 50 ... s on like this all
sry i was not memtion above help me

Member 9846414 8-Mar-13 8:13am

in stdentadress and student detatils tablelike same way has been they help me

Member 9846414 9-Mar-13 0:22am

help me i was change i want to display like as shown above

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MalwareTrojan · Answer 1 · 2013-03-08T00:38:00

Solution 1

Try this

SQL

SELECT SR.stdRClass ,SD.stdDname ,SD.stdDMarks ,SA.dist,SA.Aera
FROM studentregistration SR
INNER JOIN studentDetails SD ON SR.stdRid = SD.stdDid 
INNER JOIN studentAdress SA ON SD.stdDid = SA.stdAid
WHERE SR.stdRid='1' and SD.stdDid ='1' and SA.stdAid ='1'

Posted 8-Mar-13 0:38am

MalwareTrojan

Updated 8-Mar-13 0:45am

v2

Comments

Member 9846414 9-Mar-13 0:11am

thanks for answering i have tride this but some problem is occuring .i was not ment that in my table in studentdetails and studentAdress have Repation of rows are there plz see above again i was mention help me for this

MalwareTrojan · Answer 2 · 2013-03-08T18:53:00

Solution 2

Try Using keyword Distinct in select query. . .

Posted 8-Mar-13 18:53pm

MalwareTrojan

Comments

Member 9846414 9-Mar-13 1:39am

i have tried that also but same was geting i mention above .can u to tell me how to solve this

MalwareTrojan 9-Mar-13 1:55am

Try doing it by Primary Key- Foreign Ky Concept...
I think you need to add column for Foreign key.

Member 9846414 9-Mar-13 1:58am

i was used ditinct key in qurey like this
SELECT Distinct SR.stdRClass ,SD.stdDname ,SD.stdDMarks ,SA.dist,SA.Aera
FROM studentregistration SR
INNER JOIN studentDetails SD ON SR.stdRid = SD.stdDid
INNER JOIN studentAdress SA ON SD.stdDid = SA.stdAid
WHERE SR.stdRid='1' and SD.stdDid ='1' and SA.stdAid ='1'
group by SR.stdRClass ,SD.stdDname ,SD.stdDMarks ,SA.dist,SA.Aera
but it not was slove

MalwareTrojan 9-Mar-13 2:04am

Remove Group By Clause

MalwareTrojan 9-Mar-13 3:06am

is it working properly now?

Davidduraisamy · Answer 3 · 2013-03-08T20:07:00

Solution 3

Hi,

Check this:

SQL

create table codepro(id int,name varchar(20),marks int)

create table Codepro1(id int,dist varchar(20),Area varchar(20))

insert into codepro values(1,'davud',50),(1,'davud',30),(1,'davud',40),(2,'davud',50),(2,'davud',30)
insert into Codepro1 values(1,'nama','selli'),(1,'namakkal','selliyayee'),(1,'nama','karai'),(2,'nama','selli'),(1,'nama','selliyayee')

select * from codepro
select * from codepro1;


WITH CTE(id,marks,name,Rowid)
AS
(
SELECT id,marks,name,ROW_NUMBER()over(partition by C.id ORDER BY C.id)'Rowid' FROM codepro C
),
CTE1(id,area,dist,Rowid1)
as
(
SELECT id,Area,dist,ROW_NUMBER()over(partition by c1.id ORDER BY c1.id)'Rowid' FROM codepro1 c1
)

select C.id,C.marks,C.name,C1.area,C1.dist from CTE C INNER JOIN CTE1 C1 ON C.Rowid=C1.Rowid1 AND C.id=C1.ID order by C.id

Posted 8-Mar-13 20:07pm

Davidduraisamy

Comments

Davidduraisamy 9-Mar-13 2:48am

Whether its working fine...

Member 9846414 9-Mar-13 5:30am

thank u veryyyyy much . what i am acepting it was came but it was displaying all the details
can u tell i want only one person then where i should mention where id='1' like this thank u very much

Member 9846414 9-Mar-13 6:14am

hi sir it was came for particular id in this only tables combing and display another table i s missing that studentRegistration table from that stdRClass coloumn also been display . can u tell me ?plz edit above add one more table plz tell me i was done but confuse me

Member 9846414 9-Mar-13 6:15am

here 2 table are joing but i want to join one more table i mention above that is stdentregiration from that i wnt only stdRclass colounm can u tell me sir

Member 9846414 11-Mar-13 2:26am

help me i want to join one more table that is student registatraion fro that stdRClass A plz see the output above i was mention help me plz i was trid near joining wrote ike this
With CTE(id,StdRclass,Rowid)
As
(
SELECT id,stdRcalss,ROW_NUMBER()over(partition by C.id ORDER BY C.id)'Rowid' FROM StduentRegistration C
),

CTE1 (id,marks,name,Rowid)
AS
(
SELECT id,marks,name,ROW_NUMBER()over(partition by C1.id ORDER BY C1.id)'Rowid' FROM codepro C1
),
CTE2(id,area,dist,Rowid1)
as
(
SELECT id,Area,dist,ROW_NUMBER()over(partition by c2.id ORDER BY c2.id)'Rowid' FROM codepro1 c2
)

select C.StdrClass, C1.id,C.marks,C1.name,C2.area,C2.dist from CTE C INNER JOIN CTE1 C1 ON C.Rowid=C1.Rowid1 AND C.id=C1.ID ,CTE1 C1 INNER JOIN CTE2 C2 ON C1.Rowid= C2.Rowid and C1.id=C2.id where C.id ='1'order by C.id

but diff oup is coming only one row is duplicating .i want to display one more coloumn that is stdRclass in fornt id what u said it was working fine nice.but when i am trying to add one more it was not getting for this help me