March 5, 2010 at 12:44 am
I have Three Tables
Table 1 = School
Name rollnoResultClassCaptain
Student1001PASSNULL
Student2002PASSNULL
Student3003PASSNULL
Student4004PASSNULL
Student5005FAILNULL
Class1006PASS003
Class2007FAIL004
A student may or may not be a apart of class and the relationshsip is defined in a different table. A studet can be a part of multiple classes as given below:
Table 2 = StudentClass
group_rollstud_roll
006001
006002
006005
007003
007004
006002
Relation is defined as
School.ClassCaptain = School.rollno
StudentClass.group_roll = School.rollno
StudentClass.stud_roll = School.rollno
Table 3 = BestStudent
roll_nobest_stud
001NULL
002NULL
003NULL
004NULL
005NULL
006001
007002
BestStudent.roll_no = School.rollno
BestStudent.best_stud = School.rollno
I want to find the following :
Class_Name/Class_Result/Student_Name/Student_Result/ClassCaptain_Name/ClassCaptain_Result/BestStudent_Name/BestStudent_Result/StudentcountInClass
Class1/PASS/student1/PASS/student3/PASS/Student1/PASS/4
Class1/PASS/student2/PASS/student3/PASS/Student1/PASS/4
Class1/PASS/student5/FAIL/student3/PASS/Student1/PASS/4
Class2/FAIL/student3/PASS/student4/PASS/Student2/PASS/2
Class2/FAIL/student4/PASS/student4/PASS/Student2/PASS/2
Class1/FAIL/student2/PASS/student4/PASS/Student1/PASS/4
Someoe please help me solve this puzzle. I'd really appreciate. When i Find one thing i tend to loose another.Here is what I could assemble (only the simple parts and need help with the complex one)
select b.name [Class_Name],
b.Result [Class_Result],
c.name [Student_Name],
c.Result [Student_Result],
d.name [ClassCaptain_Name],
d.Result [ClassCaptain_Result],
d.name [BestStudent_Name],
d.Result [BestStudent_Result],
f.name [BestStudent_Name],
f.Result [BestStudent_Result],
count(*) as countitem [StudentCountInClass]
from studentclass a
INNER JOIN school b ON a.group_roll = b.contact_uuid
INNER JOIN school c ON a.stud_roll = c.contact_uuid
INNER JOIN school d ON b.classcaptain = d.contact_uuid
INNER JOIN BestStudent e ON b.best_stud = e.contact_uuid
INNER JOIN school f ON a.group_roll = b.contact_uuid
Group by b.name
March 8, 2010 at 3:54 am
It was bit hard to understand the requirement for me. May be thats one of the reasons why you did not get any response to this topic. Data provided should be consumable. Please look at this link on how to post the data. Thanks. 🙂
http://www.sqlservercentral.com/articles/Best+Practices/61537/
---------------------------------------------------------------------------------
March 8, 2010 at 11:55 pm
Thanks for the feedback. I figured it out. There were mistakes in my post as well which i corrected following the best practices link. Thanks Guys 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply