getting all rows from left table
-
Please take a look at my code below.
I want to get all the matching rows from the student table (where ...Kindergarten/School 101) which works as long as the 'testscore.administration' is NOT included in the where clause.
As testscore rows get entered into the testscore table - I want a query that will still get me a) all students in the school/grade - but also b) if there's a testscore row for the student - it will *only* return the row where testscore.administration = 1 - and not any rows for that student where testcore.administration is another quantity.
Thanks for any ideas.
-------------------------------------
SELECT Student.StudentID, TestScore.Score1, TestScore.Administration
FROM Student LEFT OUTER JOIN
TestScore ON Student.StudentID = TestScore.Student_ID
WHERE (Student.Grade = '00') AND (Student.School = 101) AND (TestScore.Administration = 1)
------------------------------------
-
When you use an outer join you can't specify criteria for the right table in the where clause. You need to re-write your query as,
SELECT Student.StudentID, TestScore.Score1, TestScore.Administration
FROM Student LEFT OUTER JOIN TestScore
ON Student.StudentID = TestScore.Student_ID
AND TestScore.Administration = 1
WHERE (Student.Grade = '00') AND (Student.School = 101)Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 09/09/2003 5:40:05 PM
--------------------
Colt 45 - the original point and click interface
-
Thanks much - works perfectly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply