Click here to Skip to main content
14,732,788 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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

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
Updated 8-Mar-13 20:38pm
v6
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

Try this
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'
   
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
Try Using keyword Distinct in select query. . .
   
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?
Hi,

Check this:

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
   
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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900