I am having a bit of trouble here. Perhaps I am just "missing" something. I will do my best to explain the situation and appreciate your patience
I have a table that holds sectionID's that have teacherID's and start/end dates. For example, section 123 was taught by teacher 456 from 1/1/2018 to 3/12/18. Then section 123 is being taught by teacher 789 from 3/13/2018 to current. I am encountering situations where the startdate for section 123 taught by teacher 456 is null. So, on my join to this table with a sectionID, I am asking and start date is not null.
Table : Classes
Fields : classID --> history, sectionID --> 123
Table : Sections
Fields : sectionID --> 123, TeacherID --> 456, startdate --> null, enddate --> 3/12/2018
sectionID --> 123, TeacherID --> 789, startdate --> 3/13/18, enddate --> null
I want to join Sections to Classes so I have ...Classes c left join Sections s on s.sectionID = c.sectionID and startdate is not null
Problem I was getting the 2 records for section 123 so I added the ...and Sections.startdate is not null. The record counts were reduced correspondingly. The query then joins to Staff on TeacherID to get the teacher name.
Prior to adding "and Sections.startdate is not null" I was getting the Teacher's names. But after adding now I am not getting the Teacher's names.
Are there any "special" Join rules that may be causing this type of misbehavior? Please ask questions.