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.

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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

FROM Section
   ON Section.TeacherID = Teacher.TeacherID
   ON Teacher.StaffID = Staff.StaffID


FROM Section
      ON Teacher.StaffID = StaffID
   ON Section.TeacherID = Teacher.TeacherID
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.
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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.
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 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,
Brian CroweDatabase 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jimbo99999Author Commented:
Thank you all for your feedback.
Mark WillsTopic 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 ?
Jimbo99999Author Commented:
Thank you for your insight on my question.  I always enjoy learning from conversations with the experts.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.