baskaran chellasamy

baskaran chellasamy

  • NA
  • 114
  • 150.9k

problem in join query

Nov 3 2012 1:22 PM
Hi friends
I have four tables
1.Stduent_academic_details.
fields: 1:AdmissionNumber 2.sectionId,3.classid, 4. Academicyearid
Admissionnumber is a foriegn key for studentAdmission table
Sectionid is a foriegn key for sectiondetails
classid is a foriegn key for standered datails
academicyareid is a foriegn key for academicyaer table

2.StudentAdmissiondateais
fields: Admissionnumber ,studentname and extra
3.Standereddetails
fields: Standered_id,standername,academicyearid and etc
4.Sectiondetails
fields: sectonid standeredid(foriegnkey),sectionname,academicyearid
from the first table i want to select all the data based on the classname and particular year.
the return data like the below
AdmisionNumber Studentname standeredname sectionname.
 
the first table contain data as below
 
AdmissionNumber sectionid calssid Academicyearid
100 50 11 01
 
Academicyearid 01 for 2012 to 2013
classid 11 for first class
sectionid 50 for A
admissionnumber 100 for studentname XXXX
 
the below is my stored procedure.but it return all the sections like a,b,c for single student.please solve this stored procedure
ALTER procedure [dbo].[sp_bindstudentsection](@classid int,@fromyear int,@toyear int)
as
begin
 declare @acid int
 select @acid=Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear
select SA.AdmissionNumber,SS.StudentName,St.Standered_name,Se.Section_name from Student_Acadamic_details SA inner join StudentAdmission_details SS on SA.AdmissionNumber=SS.AdmissionNumber inner join Standered_details St on SS.ClassId=St.Standered_id inner join Section_details Se on St.Standered_id= Se.Standered_id where SA.Acadamic_year_id=@acid and SA.classid=@classid
 end
 GO  
this is the output of my join query
100 Chandru FirstClass A
100 Chandru FirstClass B
100 Chandru FirstClass A
100 Chandru FirstClass B
But in studentAcadamic table
AdmissionNumber sectionid standereid
100 51 12
Here 51 for section A and 12 for firstclass so I want to return as
  Admissionnumber studentname classname sectionname
100 chandru firstclass A

In the above query St.standered_id=Se.Standerd_id match for four rows in each table. I think so it return like above. but i want to return data in the condition based on  classid in the  student Academic details