Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I will get a model to databases. This is less than 1.5 continued.
00:05
We're gonna look at another version of the one too many
00:08
by looking at a self reference. Now, if you're looking at the diagram of the database we've been working with, you might notice something looked a little interesting.
00:15
And the employees table. You'll notice that there seems to be a relationship in which it connects to itself, where it looks like we have a one too many,
00:24
and that's exactly what it is. It's a self reference to itself, but how would we query that out?
00:29
Well, you would just treat it like two tables and you would give each table in Alias so that it isn't ambiguous. And let me show you what I'm talking about.
00:39
And we can tell that by this relationship we could get the employee supervisor.
00:44
And this is a common way that I've seen databases do the employee supervisor relationship. In fact, at my current work environment,
00:52
that's exactly how the supervisor relationship is quarried out of that database. So let's go ahead and bring that up. I still have D Beaver open from the previous lesson.
01:03
If we right Click employees and Click View diagram.
01:08
We'll see that relationship right here,
01:11
and it does look a little bit weird. But
01:14
even with that, we go ahead. Inquiry it out. Click Sequel Editor. New Sequel Editor
01:19
Ellis
01:21
Let's put in our select statement. Select. I don't know what I Want yet, so leave that empty for now from
01:27
employees. Now here's where we have to give it an alias. So it's gonna be space
01:33
alias name.
01:34
And you know what?
01:36
Let's let's let's make it very clear. So we'll put subordinate
01:41
as the alias you could put anything you want is the alias you could Ah, you could put Cyber Re Rocks is Alias and it would work So it's anything you want.
01:51
So Kama we need the employees table again because that reference itself
01:56
and we'll put the alias as supervisor.
02:00
All right, now we would get a Cartesian product, which would not be good for what we're trying to get out, and we'll put where
02:08
subordinate
02:10
dot
02:13
reports too,
02:15
equals supervisor.
02:17
Got
02:19
employee number.
02:21
Okay,
02:22
now, let's go ahead and get the info we want. So select subordinate
02:27
dot
02:30
first name
02:30
and then subordinate
02:34
dot Job title
02:38
Comma. Now let's get the supervisor name, supervisor
02:43
dot
02:45
first name and let's give this column and alias similar to how we gave a table Alias
02:51
and we'll put as
02:53
supervisor
02:57
name
02:58
and you could take whatever you want it. I'm just trying to type in what makes sense here.
03:02
Comma supervisor
03:06
dot supervisor job title
03:08
as
03:09
supervisor.
03:13
Job title.
03:15
Let's go ahead and hit play and see what we get,
03:19
all right,
03:21
so we can see that we got a list of employees back, and this does seem to make sense like we got married.
03:27
Who's VP
03:28
of sales? Who reports to Diane, who is the president,
03:32
So I mean, that makes sense. Luckily, the test data that is provided with this test database
03:38
lines up quite nicely that Leslie, who's a sales rep,
03:43
who's, ah, supervisor name is Anthony.
03:46
Who's the sales manager
03:49
Now, let's see here we got to Leslie's.
03:52
That's weird. Maybe they're different. Let's see if they have a different last name. Let's just pop in the subordinate last name.
03:59
You see if we get a difference there.
04:02
All right, so I've added subordinate last name
04:05
gonna run that.
04:08
And sure enough, there's two
04:11
different Leslie's with different last names.
04:14
And so this is a good example of how you might get the supervisors out of
04:18
a database or, better stated, get the employees with their supervisors on a report out of the database.
04:27
Um, this again was a one too many relationships.
04:30
I hope you got this working. I hope you enjoy this extension of less than 1.5, where we took another look at the one too many
04:38
involving a table that used a self reference for the relationship. I hope you enjoyed it, and I'll see you in the next lesson.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor