Question for SQL GURU
-
Would anyone please show me how to write one SQL statement, and get all the data (including student name, English, Math, Computer, History class, and grades for each class
thanks,
I really appreciate your help
-
Hi Vanessa,
We'd be glad to help - how about posting your best effort so far, we'll offer suggestions from there?
Andy
-
Pretty Simple!
select students.name, grades.class, grades.grade from students, grades where students.studentid = grades.studentid order by students.name
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India -
Pretty Simple!
select students.name, grades.class, grades.grade from students, grades where students.studentid = grades.studentid order by students.name
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India -
Alternatively,
SELECT Student.Name, Grades.Class, Grades.Grade
FROM Grades INNER JOIN
Student ON Grades.studentid = Student.StudentID
-
Cmon guys - believe it or not, I COULD have written the SQL. We're not here to do homework or class assignments or help anyone else that hasn't tried first. I don't know if Vanessa falls into that category or not, but it was her first post and the problem was fairly simple AND involved grades.
Our philosphy here is to help our readers learn. That means getting them to try to solve it first and in some cases even digging a bit to see what the problem really is - especially with beginners they are often trying to solve a problem created by their own idea of a solution when another solution would avoid that problem all together. Obviously to what extent we do this depends on what we perceive as the skill level of the question asking - if Steve Jones asks for help with TSQL, I doubt its because he doesnt know how to do a join!
Along those lines, let's use ANSI SQL - no joins in the where clause!
Andy
-
Andy you are correct. Just a suggestion... make a group/room for beginners and let them play around there.
quote:
Cmon guys - believe it or not, I COULD have written the SQL. We're not here to do homework or class assignments or help anyone else that hasn't tried first. I don't know if Vanessa falls into that category or not, but it was her first post and the problem was fairly simple AND involved grades.Our philosphy here is to help our readers learn. That means getting them to try to solve it first and in some cases even digging a bit to see what the problem really is - especially with beginners they are often trying to solve a problem created by their own idea of a solution when another solution would avoid that problem all together. Obviously to what extent we do this depends on what we perceive as the skill level of the question asking - if Steve Jones asks for help with TSQL, I doubt its because he doesnt know how to do a join!
Along those lines, let's use ANSI SQL - no joins in the where clause!
Andy
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India -
Sorry, but my question isn't clear. when using a join, or statement mentioned above, I will get duplicated data like the following
ChrisEnglishB
ChrisMathB
ChrisComputerB
ChrisHistoryA
MikeEnglishA
MikeMathA
MikeComputerB
MikeHistoryC
SusanEnglishC
SusanMathC
SusanComputerA
SusanHistoryC
My question is, is it possible to have data returns in the following format:
Chris,English,B, Math,B, Computer, B, History, B .
That way, when using DO Until recordset.EOF in ASP, I can just write:
Response.Write Recordset("name"), Recordset("English"),Recordset("Englishgrade"),Recordset("Math"),Recordset("MathGrade"),Recordset("Computer"),Recordset("ComputerGrade"),Recordset("History"),Recordset("HistoryGrade")
So far, what I did was to :
Select * from student .
Then I do a loop like the following:
While NOT Recordset.EOF
REsponse.write(Recordset("name"))
Set RSClass = Conn.execute("Select Class, Grade from grades where StudentID = "& Recordset("StudentID") &"
Do until StudentID.EOF
Response.Write RSClass("Class") & RSClass("Grade")
RSClass.MoveNext
Loop
Recordset.MoveNext
Wend
--------------------
This works but it is VERY Slow because it has to make so many connections to the database to get the grade for each student. I was hoping to see if there is another way to use only 1 SQL statement to get all the data..
Thanks
-
What you need to do is handle this in the ASP.
Use your loop to check for the name and only print it (and a new line) if the name has changed.
Not sure if this is a school assignment or work, but handling output like this in a loop is very basic computer science. I'd like to see you work it a little (as Andy mentioned). We are happy to help, but this is really a trivial problem if you are concerned about output.
The SQL to do this would be unnecesarily complex, though it could be done. You would have to use a temp table to handle the unknown number of matches in the second table.
Alternatively, use 2 queries. One to get the students and one to get the grades. Order them the same and match them up in ASP.
Steve Jones
-
Vanessa, Steve has suggested you the correct options to keep your work going on for now... but you need to be clear with the SQL fundamentals.
Please go thru the tutorials floating around on the net which would help you to use SQL Server with ASP in a much better way than you would be using now.
Just search for them and keep yourself updated with it... you'll find loads of it.
Good Luck!
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India -
Vannessa
It is possible to do this with a single query. I'll give you a hint; it involves a two step process. First you need to do four left joins with the grades table. Giving you a results in the form of Studentid,Class1,Grade1,Class2,Grade2,Class3,Grade3,Class4,Grade4. Second you need to join the students table to the results where class1='English'
-
Clearer Please!
quote:
VannessaIt is possible to do this with a single query. I'll give you a hint; it involves a two step process. First you need to do four left joins with the grades table. Giving you a results in the form of Studentid,Class1,Grade1,Class2,Grade2,Class3,Grade3,Class4,Grade4. Second you need to join the students table to the results where class1='English'
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India -
In this solution I am assuming everyone has a grade for english
select studentid,class1,grade1,class2,grade2,class3,grade3,class4,grade4 from
(select g1.studentid,g1.class class1,g1.grade grade1,
g2.class class2,g2.grade grade2,g3.class class3,g3.grade grade3,
g4.class class4,g4.grade grade4
from grades g1 left join grades g2
on g1.studentid=g2.studentid and g1.class='english' and g2.class='math'
left join grades g3 on g3.studentid=g2.studentid and g3.class='computer'
left join grades g4 on g4.studentid=g3.studentid and g4.class='History') STG
where class1='english'
-
quote:
In this solution I am assuming everyone has a grade for englishselect studentid,class1,grade1,class2,grade2,class3,grade3,class4,grade4 from
(select g1.studentid,g1.class class1,g1.grade grade1,
g2.class class2,g2.grade grade2,g3.class class3,g3.grade grade3,
g4.class class4,g4.grade grade4
from grades g1 left join grades g2
on g1.studentid=g2.studentid and g1.class='english' and g2.class='math'
left join grades g3 on g3.studentid=g2.studentid and g3.class='computer'
left join grades g4 on g4.studentid=g3.studentid and g4.class='History') STG
where class1='english'
the following select will do the same
select g1.StudentID,g1.class, g1.grade, g2.class, g2.grade, g3.class, g3.grade, g4.class, g4.grade
from #grades g1
join #grades g2
on g1.StudentID = g2.StudentID
and g1.class = 'English' and g2.class = 'Math'
join #grades g3
on g2.StudentID = g3.StudentID
and g3.class = 'Computer'
join #grades g4
on g4.StudentID = g2.StudentID
and g4.class = 'History'
-
Output Produced
--------------------------------------------------
Mike,English,A,English,A,Math,A,History,A
--------------------------------------------------
Susan,English,B,Math,C,History,D
--------------------------------------------------
Chris,English,A,Math,B,History,E
--------------------------------------------------
Peet,English,A
-- Table LenS = Students
-- Table LenG = Grades
Set Nocount On
Declare @Result varchar(50),
@i Int
-- Get first student id
Select @i=Min(Id)
From LenS
-- Do all students
While IsNull(@i,"")<>""
Begin
-- Init variable by setting to student name
Select @Result=Name
From LenS
Where Id=@i
-- Accumulate grades for this student
Update LenG
Set @Result=@Result + ","+
Class +
","+
Grade
Where [id]=@i
-- Give it to the user
Select @Result
-- Get next student and repeat the loop
Select @i=Min([Id])
From LenS
Where [Id]>@i
End
Set Nocount Off
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply