Trouble with null Date and Join

Hello Experts!

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.

ISSUE
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.

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
I opened your document and knew immediately that it was written by a programmer (seven WHERE clauses embedded in a single query) and not a database person.  I'm just surprised that I don't see references to half a dozen different temp tables.  I see this all the time at work from our own developers who are great developers but don't really "get" set-based logic.

Deciphering this conglomeration would require a thorough understanding of your schema and the data.  The first thing I would recommend would be to separate some of this logic out into cte's (common table expression) for the sake of clarity and maintenance.
0
 
Jimbo99999Author Commented:
I changed the "and Sections.startdate is not null" to "and Sections.enddate is null" and now I get the teacher names.  But I don't want to rely on this as there could be a date out in the future for an enddate.
0
 
Brian CroweDatabase AdministratorCommented:
You either need to LEFT JOIN to Staff or you need to nest the INNER JOIN inside the LEFT JOIN to Teacher...

SELECT ...
FROM Section
LEFT OUTER JOIN Teacher
   ON Section.TeacherID = Teacher.TeacherID
LEFT OUTER JOIN Staff
   ON Teacher.StaffID = Staff.StaffID

OR

SELECT ...
FROM Section
LEFT OUTER JOIN Teacher
   INNER JOIN Staff
      ON Teacher.StaffID = StaffID
   ON Section.TeacherID = Teacher.TeacherID
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Brian CroweDatabase AdministratorCommented:
I forgot to ask if you are specifying "and Sections.startdate is not null" in the JOIN or in the WHERE ?  It does make a difference when you are using OUTER JOIN's.

It would be helpful if you posted the query instead of sections of it in prose.
1
 
Mark WillsTopic AdvisorCommented:
No special joins, just needing to understand what you are trying to achieve.

Sometimes, you may need to sort out the data elements first. And what your intent is with the data elements.

For example IS NOT NULL in the where, means you are effectively making the join more like an inner join.

But you can check for NULLs and make an appropriate substitution to help the correct selection processes happen. This could even be in the main select, not the join, not the where..

Clearly, the nature of the query changes dramatically with the smallest inclusion of the word "NOT" it is the difference between getting the teachers name or not.

So, we need to understand what you are trying to achieve.

Can you please post your query and what you want as results.

If not the full query, at least a codified version of the example in your question, along with expected results.
0
 
Jimbo99999Author Commented:
Thank you for your responses.  I am only at the company 2 days a week creating queries for reports and maximizing existing ones. I have attached a file.

The results for this query had records being duplicated.  I tracked down one source and then found this issue earlier today.  In the attached file, the second last line joining to sectionStaffHistory is where the problem is.  There is to be only 1 record returned for each section.  Records are kept by expiring them with the endDate and then taking out a new record with, most likely, a new Teacher.  

However, in this case, there was nothing excluding the record that had been expired...so I am left with the join picking up both of them.  
When the expiring was done and the new record taken out, something must have gone wrong as the startDate for the expired records was NULL.  

So, by adding "and Sections.startdate is not null" to the JOIN on the second last line, I was trying to only get the current record.  I know there needs to be more like "AND ((GetDate() >= startdate and enddate is null) or (GetDate() >= startdate and GetDate()" but I was just trying to do something simple to only get the current record...kind of like a proof of concept before creating a more elegant way to do it.  

Thank you for your help,
jimbo99999
EE31318.docx
0
 
Jimbo99999Author Commented:
Thank you all for your feedback.
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well, I slightly disagree with Brian. Thats the same way I approach writing a proof of concept. You add a bit more, you join additional requirement, you make sure you get the columns needed, then rationalise and streamline, and look for performance gains.

But then again, my secret in this place is a long history of programming. So, I guess he might be correct after all :)

I cannot see anything totally glaring other than what you have mentioned. Which I can well imagine.

Brian is also correct in saying there is a need to understand the data relationships. It does seem that left joins have been used "just in case" where the relationship should be well established despite that.

One thing I would be inclined to do is eliminate NULLS not by checking IS NULL, but using isnull(column_name,getdate())  type of defaulting to a value.

The difficulty with LEFT JOINS is a resulting NULL and trying to differentiate between a found rowset which has a null value versus a missing rowset is the challenge - so - you might need to check and differentiate between a null row and a null column.

While prototyping, do that in the select - something like checking a primary key value if that is null then it is an empty rowset.

Does that make sense ?
0
 
Jimbo99999Author Commented:
Thank you for your insight on my question.  I always enjoy learning from conversations with the experts.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.