Click here to Skip to main content
11,491,598 members (66,913 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi friends

I have four tables
1.Student_academic_details.
fields:
AdmissionNumber
sectionId,
classid,
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.StudentAdmissiondetails
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
--select Sa.AdmissionNumber,Sa.StudentName,St.Standered_name,Se.Section_name from StudentAdmission_details Sa inner join Standered_details St on Sa.ClassId=St.Standered_id inner join  Section_details se on St.Standered_id=se.Standered_id where St.Standered_id=@classid
--declare @studentname varchar(50),@classname varchar(30),@sectionname char(1),@admissionnumber varchar(50)
--select @sectionname=Section_name from Section_details where Standered_id=@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
Posted 2-Nov-12 23:11pm
Edited 4-Nov-12 8:45am
Maciej Los183.4K
v4
Comments
snehasish nandy at 3-Nov-12 4:17am
   
what is the problem u r getting..?
baskaran chellasamy at 3-Nov-12 4:33am
   
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
Tadit Dash at 3-Nov-12 10:18am
   
Can u please post all the data present in the tables along with the column headers ?
That would be helpful to trap the bug. :P

Thanks...
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

you will require proper join with proper set of conditions
try this,
select SA.AdmissionNumber,SS.Stu>dentName,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 and se.section_Id=sa.section_Id and se.acid=st.acid 
where SA.Acadamic_year_id=@acid and SA.classid=@classid

Happy Coding!
Smile | :)
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 476
1 Maciej Los 276
2 Sascha Lefèvre 275
3 Andy Lanng 263
4 OriginalGriff 215
0 Sergey Alexandrovich Kryukov 9,829
1 OriginalGriff 8,530
2 Sascha Lefèvre 3,579
3 Maciej Los 3,332
4 Richard Deeming 2,370


Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 4 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100