2.5 One to Many Relationships Part 2

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