1
00:00:05,000 --> 00:00:12,200
In this lecture we're going to go back and edit our operator Defect Entries view to include information

2
00:00:12,200 --> 00:00:16,640
about our repair and supervisor staff members.

3
00:00:16,640 --> 00:00:24,530
And if you recall, the record that we have in our production defect tracking table just has null values

4
00:00:24,530 --> 00:00:28,640
for our repair information and for supervisors.

5
00:00:28,640 --> 00:00:32,030
And that's because we haven't built the interfaces for them yet.

6
00:00:32,030 --> 00:00:34,910
But our view has to be able to handle nulls.

7
00:00:34,910 --> 00:00:42,650
And if you recall, when we first built the view, when we added in the repair or supervisor people

8
00:00:42,650 --> 00:00:46,400
that had nulls, it returned no records down here.

9
00:00:46,400 --> 00:00:54,800
And that's because by default, the view editor creates everything as an inner join when you add the

10
00:00:54,800 --> 00:00:55,550
table.

11
00:00:55,670 --> 00:00:59,090
So let's take a closer look at the syntax here.

12
00:00:59,090 --> 00:01:07,760
And I am just going to kind of refactor this SQL query just so we can kind of focus on the syntax of

13
00:01:07,760 --> 00:01:08,240
the join.

14
00:01:08,240 --> 00:01:11,060
We're going to learn more about this in the next section.

15
00:01:11,060 --> 00:01:18,080
But when the SQL builder creates this it kind of puts the inner join at the end of every line.

16
00:01:18,080 --> 00:01:20,630
And it's not all that intuitive.

17
00:01:20,630 --> 00:01:26,210
And so I'm going to put all the inner joins here to show our tables.

18
00:01:26,210 --> 00:01:32,330
And this is the one that I want to focus on where we're getting our operator.

19
00:01:32,330 --> 00:01:33,380
Staff member.

20
00:01:34,300 --> 00:01:38,530
So let's just look at this and the way we look at this.

21
00:01:38,530 --> 00:01:39,940
For our staff.

22
00:01:39,940 --> 00:01:47,410
It's saying join our staff table to our production defect tracking table where the operator ID has the

23
00:01:47,410 --> 00:01:51,700
exact same entry as our staff table ID.

24
00:01:51,820 --> 00:02:01,840
So when thinking about joins our production defect tracking is considered the left table and our staff

25
00:02:01,840 --> 00:02:03,880
is considered our right table.

26
00:02:03,880 --> 00:02:13,510
So when we do an inner join, it is only going to show records where we have the same value in our left

27
00:02:13,510 --> 00:02:15,250
table and our right table.

28
00:02:15,250 --> 00:02:17,920
And going back to look at our original table.

29
00:02:17,920 --> 00:02:24,040
If you look at Repair station ID, it is also going to be joined to the staff table, just like our

30
00:02:24,040 --> 00:02:25,390
operator ID was.

31
00:02:25,390 --> 00:02:29,110
But obviously it's looking for different levels of entry.

32
00:02:29,110 --> 00:02:36,400
So we're going to have to add another instance of our staff table for our repair ID and our supervisor

33
00:02:36,400 --> 00:02:36,790
ID.

34
00:02:36,790 --> 00:02:42,100
Same thing for our repair station ID and supervisor station ID.

35
00:02:42,100 --> 00:02:45,340
And so we can actually name these instances.

36
00:02:45,340 --> 00:02:51,460
This staff table is what we're using for our operator ID.

37
00:02:52,240 --> 00:02:58,390
And so if we just right click and do properties we can give this table an alias.

38
00:02:58,390 --> 00:03:00,670
And this might make it a little clearer.

39
00:03:00,670 --> 00:03:03,130
So I'll do operator staff.

40
00:03:03,130 --> 00:03:11,590
And when I do this then we can see that our join renames our staff using an alias as operator staff.

41
00:03:11,590 --> 00:03:15,940
And it's a little easier to see exactly what this join is doing.

42
00:03:15,940 --> 00:03:22,390
It's looking for our operator staff, where our operator ID and our defect tracking table has the same

43
00:03:22,390 --> 00:03:25,870
entry as the ID and our operator staff.

44
00:03:25,960 --> 00:03:32,500
That's going to be okay that we leave this in inner join, because operators are entering the data and

45
00:03:32,500 --> 00:03:34,360
they have to put in themselves.

46
00:03:34,360 --> 00:03:38,230
So we're never going to have a situation where there's a null there.

47
00:03:38,230 --> 00:03:43,840
But let's go ahead and add in another staff table for our repair ID.

48
00:03:43,840 --> 00:03:46,870
And remember all of these values right now are null.

49
00:03:46,870 --> 00:03:49,960
But we need to be able to show these whether they're null or not.

50
00:03:49,960 --> 00:03:53,290
So I'm just going to right click and I'll add table.

51
00:03:53,290 --> 00:03:55,180
And I'm just going to put staff.

52
00:03:55,180 --> 00:04:01,420
But I am going to create an alias here as repair staff.

53
00:04:01,420 --> 00:04:04,690
And when we add the same table in.

54
00:04:05,240 --> 00:04:06,680
In our sequel.

55
00:04:06,680 --> 00:04:14,480
Of course, it tries to do a join on all of the fields in our production defect table that use staff.

56
00:04:14,480 --> 00:04:21,890
So it's got our production tracking repairman, which we want, but it also puts in our operator ID

57
00:04:21,890 --> 00:04:23,780
and our production ID.

58
00:04:23,780 --> 00:04:32,240
So just like last time, we're going to get rid of the extra values and just do our join for repair

59
00:04:32,240 --> 00:04:36,950
staff on our repairman ID equals our repair staff ID.

60
00:04:36,980 --> 00:04:43,430
Now, if I run this right now, we're not going to get any records because this is an inner join.

61
00:04:43,430 --> 00:04:45,860
Let me drop this down to this line here.

62
00:04:45,860 --> 00:04:47,570
But this is an inner join.

63
00:04:47,570 --> 00:04:53,150
And an inner join only returns records where we have the same value here and here.

64
00:04:53,150 --> 00:04:57,440
In this case we have a repairman ID that is null.

65
00:04:57,440 --> 00:04:59,840
And so there is no null staff ID.

66
00:04:59,990 --> 00:05:04,400
So all I really have to do is I need to change this to an outer join.

67
00:05:04,400 --> 00:05:09,770
But I can't really just say outer because it will give me an error.

68
00:05:10,010 --> 00:05:13,910
And so you, you have to tell it whether it's the left or right.

69
00:05:13,910 --> 00:05:17,390
So we're going to make this a left outer join.

70
00:05:17,390 --> 00:05:25,520
And what that means is return everything in the production defect tracking table, even if it does not

71
00:05:25,520 --> 00:05:33,440
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,

72
00:05:33,440 --> 00:05:36,800
but we don't get our repairman and that's because we haven't told it to.

73
00:05:36,830 --> 00:05:41,810
So on our repair staff, we need to check that we want the last name.

74
00:05:42,170 --> 00:05:48,500
Notice that it pops it in down here and just gives it expression one as an alias.

75
00:05:48,500 --> 00:05:51,020
And we're going to change that to repairman.

76
00:05:51,020 --> 00:05:54,950
And then when I run it then it just gives us a null.

77
00:05:54,950 --> 00:05:59,300
And that's fine as long as we are handling the nulls that's okay.

78
00:05:59,300 --> 00:06:05,750
Once we add a repair staff ID into this, it's going to show the name, just like we show the operator

79
00:06:05,750 --> 00:06:06,290
name.

80
00:06:06,560 --> 00:06:12,590
So we're going to repeat these steps and add in the repair station.

81
00:06:12,590 --> 00:06:14,180
So I'll do add table.

82
00:06:15,400 --> 00:06:17,800
And I'll add in station.

83
00:06:17,800 --> 00:06:21,670
And then this is the new station that is put in.

84
00:06:21,670 --> 00:06:28,780
And we are going to give it an alias as repair station.

85
00:06:28,780 --> 00:06:35,200
And then of course here it's added in our new repair station.

86
00:06:35,440 --> 00:06:40,450
And we need to get rid of our tracking supervisor.

87
00:06:40,450 --> 00:06:45,430
And we also don't want the operator station because this is the repair station.

88
00:06:45,430 --> 00:06:48,460
So I'll get rid of this one.

89
00:06:48,760 --> 00:06:55,870
And so our join is going to be on our defect tracking repair station ID with our repair station ID.

90
00:06:57,050 --> 00:06:59,210
And this inner join.

91
00:06:59,210 --> 00:07:03,470
I'm just going to put it on the same line just so that it's not confusing.

92
00:07:03,470 --> 00:07:04,190
Hopefully.

93
00:07:04,190 --> 00:07:13,460
And of course we need to change this to a left outer join as well so that it does show our station.

94
00:07:13,460 --> 00:07:19,250
And then of course, for our repair station I'll choose the station name.

95
00:07:19,950 --> 00:07:25,440
And we can rerun this and we've got our repair name and our station name.

96
00:07:25,440 --> 00:07:26,520
Of course, these are null.

97
00:07:26,520 --> 00:07:27,300
That's okay.

98
00:07:27,300 --> 00:07:35,430
And we'll change our alias to Repair Station and rerun that and we'll go ahead and save.

99
00:07:35,430 --> 00:07:43,440
Now of course, since we have our repair information in in our main table, we'll want to go ahead and

100
00:07:43,440 --> 00:07:46,740
include our repair notes as well.

101
00:07:46,980 --> 00:07:50,310
And when we rerun it, we see we do have nulls for these.

102
00:07:50,310 --> 00:07:51,180
No worries.

103
00:07:51,180 --> 00:07:59,190
In the next assignment you will be adding in repair and supervisor information once you build the screens

104
00:07:59,190 --> 00:07:59,760
for them.

105
00:07:59,760 --> 00:08:09,450
Then lastly, I am just going to save our new view and then I can open a new query window and just test

106
00:08:09,450 --> 00:08:12,270
it by doing select star from.

107
00:08:12,690 --> 00:08:15,060
And I can drag our view up here.

108
00:08:15,060 --> 00:08:21,060
And when we execute it you see we do get our repair information for this record.

109
00:08:21,060 --> 00:08:26,580
Although it's null in the next assignment you're going to be doing the same steps to edit the view to

110
00:08:26,580 --> 00:08:29,130
show the supervisor information.

111
00:08:29,280 --> 00:08:31,620
So I'll see you in the next assignment.