7 hours 36 minutes
I will get a model to databases. This is less than 1.5 continued.
We're gonna look at another version of the one too many
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.
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,
and that's exactly what it is. It's a self reference to itself, but how would we query that out?
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.
And we can tell that by this relationship we could get the employee supervisor.
And this is a common way that I've seen databases do the employee supervisor relationship. In fact, at my current work environment,
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.
If we right Click employees and Click View diagram.
We'll see that relationship right here,
and it does look a little bit weird. But
even with that, we go ahead. Inquiry it out. Click Sequel Editor. New Sequel Editor
Let's put in our select statement. Select. I don't know what I Want yet, so leave that empty for now from
employees. Now here's where we have to give it an alias. So it's gonna be space
And you know what?
Let's let's let's make it very clear. So we'll put subordinate
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.
So Kama we need the employees table again because that reference itself
and we'll put the alias as supervisor.
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
now, let's go ahead and get the info we want. So select subordinate
and then subordinate
dot Job title
Comma. Now let's get the supervisor name, supervisor
first name and let's give this column and alias similar to how we gave a table Alias
and we'll put as
and you could take whatever you want it. I'm just trying to type in what makes sense here.
dot supervisor job title
Let's go ahead and hit play and see what we get,
so we can see that we got a list of employees back, and this does seem to make sense like we got married.
of sales? Who reports to Diane, who is the president,
So I mean, that makes sense. Luckily, the test data that is provided with this test database
lines up quite nicely that Leslie, who's a sales rep,
who's, ah, supervisor name is Anthony.
Who's the sales manager
Now, let's see here we got to Leslie's.
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.
You see if we get a difference there.
All right, so I've added subordinate last name
gonna run that.
And sure enough, there's two
different Leslie's with different last names.
And so this is a good example of how you might get the supervisors out of
a database or, better stated, get the employees with their supervisors on a report out of the database.
Um, this again was a one too many relationships.
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
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.