In this lecture we're going to go back and edit our operator Defect Entries view to include information
about our repair and supervisor staff members.
And if you recall, the record that we have in our production defect tracking table just has null values
for our repair information and for supervisors.
And that's because we haven't built the interfaces for them yet.
But our view has to be able to handle nulls.
And if you recall, when we first built the view, when we added in the repair or supervisor people
that had nulls, it returned no records down here.
And that's because by default, the view editor creates everything as an inner join when you add the
table.
So let's take a closer look at the syntax here.
And I am just going to kind of refactor this SQL query just so we can kind of focus on the syntax of
the join.
We're going to learn more about this in the next section.
But when the SQL builder creates this it kind of puts the inner join at the end of every line.
And it's not all that intuitive.
And so I'm going to put all the inner joins here to show our tables.
And this is the one that I want to focus on where we're getting our operator.
Staff member.
So let's just look at this and the way we look at this.
For our staff.
It's saying join our staff table to our production defect tracking table where the operator ID has the
exact same entry as our staff table ID.
So when thinking about joins our production defect tracking is considered the left table and our staff
is considered our right table.
So when we do an inner join, it is only going to show records where we have the same value in our left
table and our right table.
And going back to look at our original table.
If you look at Repair station ID, it is also going to be joined to the staff table, just like our
operator ID was.
But obviously it's looking for different levels of entry.
So we're going to have to add another instance of our staff table for our repair ID and our supervisor
ID.
Same thing for our repair station ID and supervisor station ID.
And so we can actually name these instances.
This staff table is what we're using for our operator ID.
And so if we just right click and do properties we can give this table an alias.
And this might make it a little clearer.
So I'll do operator staff.
And when I do this then we can see that our join renames our staff using an alias as operator staff.
And it's a little easier to see exactly what this join is doing.
It's looking for our operator staff, where our operator ID and our defect tracking table has the same
entry as the ID and our operator staff.
That's going to be okay that we leave this in inner join, because operators are entering the data and
they have to put in themselves.
So we're never going to have a situation where there's a null there.
But let's go ahead and add in another staff table for our repair ID.
And remember all of these values right now are null.
But we need to be able to show these whether they're null or not.
So I'm just going to right click and I'll add table.
And I'm just going to put staff.
But I am going to create an alias here as repair staff.
And when we add the same table in.
In our sequel.
Of course, it tries to do a join on all of the fields in our production defect table that use staff.
So it's got our production tracking repairman, which we want, but it also puts in our operator ID
and our production ID.
So just like last time, we're going to get rid of the extra values and just do our join for repair
staff on our repairman ID equals our repair staff ID.
Now, if I run this right now, we're not going to get any records because this is an inner join.
Let me drop this down to this line here.
But this is an inner join.
And an inner join only returns records where we have the same value here and here.
In this case we have a repairman ID that is null.
And so there is no null staff ID.
So all I really have to do is I need to change this to an outer join.
But I can't really just say outer because it will give me an error.
And so you, you have to tell it whether it's the left or right.
So we're going to make this a left outer join.
And what that means is return everything in the production defect tracking table, even if it does not
match up with what's in our staff ID now I will run the SQL and it does run and we do get a record,
but we don't get our repairman and that's because we haven't told it to.
So on our repair staff, we need to check that we want the last name.
Notice that it pops it in down here and just gives it expression one as an alias.
And we're going to change that to repairman.
And then when I run it then it just gives us a null.
And that's fine as long as we are handling the nulls that's okay.
Once we add a repair staff ID into this, it's going to show the name, just like we show the operator
name.
So we're going to repeat these steps and add in the repair station.
So I'll do add table.
And I'll add in station.
And then this is the new station that is put in.
And we are going to give it an alias as repair station.
And then of course here it's added in our new repair station.
And we need to get rid of our tracking supervisor.
And we also don't want the operator station because this is the repair station.
So I'll get rid of this one.
And so our join is going to be on our defect tracking repair station ID with our repair station ID.
And this inner join.
I'm just going to put it on the same line just so that it's not confusing.
Hopefully.
And of course we need to change this to a left outer join as well so that it does show our station.
And then of course, for our repair station I'll choose the station name.
And we can rerun this and we've got our repair name and our station name.
Of course, these are null.
That's okay.
And we'll change our alias to Repair Station and rerun that and we'll go ahead and save.
Now of course, since we have our repair information in in our main table, we'll want to go ahead and
include our repair notes as well.
And when we rerun it, we see we do have nulls for these.
No worries.
In the next assignment you will be adding in repair and supervisor information once you build the screens
for them.
Then lastly, I am just going to save our new view and then I can open a new query window and just test
it by doing select star from.
And I can drag our view up here.
And when we execute it you see we do get our repair information for this record.
Although it's null in the next assignment you're going to be doing the same steps to edit the view to
show the supervisor information.
So I'll see you in the next assignment.