November 12, 2003 at 4:27 pm
Hello,
I have a main query that will return a student ID from a table called 'Student_Data_Main' if two fields ( called 'SGmaGQ1' and 'SGrdGQ1')in the related record in another table called 'tblRCStudentGrades' are null, and the 'Status' of the record in 'Student_Data_Main' is null, and the student's 'Grade' is between first (01) and fifth (05) grade, and the student ID is not between a certain range of numbers (600-900).
The main query addresses students in the first through fifth grade, but I also need to address kindergarten students (00) with a different set of criteria. For those students, I need to also return the student ID from the 'Student_Data_Main' table if two different fields ( called 'ST2Q1' and 'ST19Q1') in the related record of the 'tblRCStudentGrades' table are null, and the 'Status' of the record in 'Student_Data_Main' is null, and the student's 'Grade' is kindergarten (00), and the student ID is not between a certain range of numbers (600-900).
The link between the records in 'Student_Data_Main' and 'tblRCStudentGrades' is the student ID field, which is called 'Permnum'. The query also returns other information about the location of the student from a table called 'tblLocation', and data about the student's teacher from a table called 'Teacher_Data_Main'.
The end purpose is to show records of students from 'Student_Data_Main' in grades 1-5 that do not have marks in 'SGmaGQ1' and 'SGrdGQ1' from 'tblRCStudentGrades', and to also show kindergarten students that do not have marks in 'ST2Q1' and 'ST19Q1' from the same table (tblRCStudentGrades).
The code below is an attempt to get this result:
************************************************
SELECT SD.Permnum AS SDPermnum, SD.Firstname, SD.Lastname,
SD.Grade, LC.Location2, LC.LocationDesc,
TD.TeacherID, TD.Firstname as TFirstname, TD.Lastname as TLastname
FROM Student_Data_Main SD
INNER JOIN tblLocation LC on SD.Schoolnum=LC.Location2
LEFT OUTER JOIN Teacher_Data_Main TD on TD.TeacherID=SD.TeacherID
LEFT OUTER JOIN tblRCStudentGrades RC On RC.Permnum = SD.Permnum
WHERE
RC.SGmaGQ1 IS NULL
AND
RC.SGrdGQ1 IS NULL
AND
SD.Status IS NULL
AND SD.Grade BETWEEN 01 AND 05
AND SD.Permnum NOT BETWEEN 600 AND 900
AND EXISTS
(SELECT SD.Permnum
FROM Student_Data_Main SD LEFT OUTER JOIN tblRCStudentGrades RC
ON SD.Permnum=RC.Permnum
WHERE SD.Grade = 00
AND RC.ST2Q1 is null
AND RC.ST19Q1 is null
AND SD.Status is null
AND SD.Permnum NOT BETWEEN 600 AND 900)
***************************************************
If I run the two SELECT statements independently of one another, I get the expected results from each query, but I have not been able to make both work together in one query.
Do you have any ideas on what I could do to get the results I am looking for?
Thank you for your help!
CSDunn
November 12, 2003 at 5:10 pm
SELECT SD.Permnum AS SDPermnum, SD.Firstname, SD.Lastname,
SD.Grade, LC.Location2, LC.LocationDesc,
TD.TeacherID, TD.Firstname as TFirstname, TD.Lastname as TLastname
FROM Student_Data_Main SD
INNER JOIN tblLocation LC on SD.Schoolnum=LC.Location2
LEFT OUTER JOIN Teacher_Data_Main TD on TD.TeacherID=SD.TeacherID
LEFT OUTER JOIN tblRCStudentGrades RC On RC.Permnum = SD.Permnum
WHERE SD.Staus IS NULL AND SD.Permnum NOT BETWEEN 600 AND 900
AND ((RC.SGmaGQ1 IS NULL
AND RC.SGrdGQ1 IS NULL
AND SD.Grade BETWEEN 01 AND 05)
OR (SD.Grade = 00
AND RC.ST2Q1 is null
AND RC.ST19Q1 is null))
--Jonathan
--Jonathan
November 17, 2003 at 11:13 am
Unless I'm missing something, you could make your life easier by treating this as two separate queries and joining the results with UNION. This is exactly the type of scenario UNION addresses.
Bob
November 17, 2003 at 4:24 pm
quote:
Unless I'm missing something, you could make your life easier by treating this as two separate queries and joining the results with UNION. This is exactly the type of scenario UNION addresses.
Thanks. I have not used UNION before, I'll try it.
CSDunn
November 18, 2003 at 2:55 am
A hint on using UNION -
Since you won't be returning any duplicates, and since you aren't ORDERing the data, you should use UNION ALL to improve performance. Otherwise SQL will perform a sort on the data to remove duplicate records, which will have a performance cost.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
November 18, 2003 at 11:52 am
quote:
Since you won't be returning any duplicates, and since you aren't ORDERing the data, you should use UNION ALL to improve performance.
Yes, I did this. The speed increase of UNION ALL vs the query I was using was very noticable. Thanks again!
CSDunn
November 18, 2003 at 1:30 pm
Why use UNION if you don't need to? A query like the one I posted will both perform better and be less wordy.
--Jonathan
--Jonathan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply