Joins within and scross tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joins within and scross tables

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-03-05 : 02:43:09

I have Three Tables

Table 1 = School
Name rollno Result ClassCaptain
Student1 001 PASS NULL
Student2 002 PASS NULL
Student3 003 PASS NULL
Student4 004 PASS NULL
Student5 005 FAIL NULL
Class1 006 PASS 003
Class2 007 FAIL 004

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_roll stud_roll
006 001
006 002
006 005
007 003
007 004
006 002

Relation is defined as

School.ClassCaptain = School.rollno
StudentClass.group_roll = School.rollno
StudentClass.stud_roll = School.rollno

Table 3 = BestStudent
roll_no best_stud
001 NULL
002 NULL
003 NULL
004 NULL
005 NULL
006 001
007 002

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 09:31:18

can you explain how you will get below row?

Class1/FAIL/student2/PASS/student4/PASS/Student1/PASS/4

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-03-05 : 11:59:47

Looks like I made a mistake. It has to be :
Class1/PASS/student2/PASS/student3/PASS/Student1/PASS/4
Does this make sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:13:29

yup now it makes sense


SELECT t1.Name,t1.Result,
t2.Name,t2.Result,
t3.Name,t3.Result,
t4.Name,t4.Result,
grp.StudCnt
FROM School t1
INNER JOIN StudentClass ss
ON ss.group_roll = t1.rollno
INNER JOIN School t2
ON t2.rollno=ss.stud_roll
INNER JOIN School t3
ON t3.rollno=t1.ClassCaptain
INNER JOIN BestStudent bs
ON bs.roll_no = t1.rollno
INNER JOIN School t4
ON t4.rollno=bs.best_stud
INNER JOIN(SELECT group_roll,COUNT(*) AS StudCnt
           FROM StudentClass
           GROUP BY group_roll)grp
ON grp.group_roll = ss.group_roll
WHERE t1.ClassCaptain IS NOT NULL