2.3 One to One Relationships Part 2

00:00

hi and welcome the module to Pleasant 1.4, the 1 to 1 relationship in more detail or continued.

00:07

So

00:08

I'm at a my sequel Demo database download site. This will be affiliated with the lesson as well for download, but you can also go to my sequel tutorial dot or slash my sequel,

00:19

Dash sample Dash database dot as PX to download as well.

00:24

So we're going to work with a skimmer that displays the 1 to 1 relationship.

00:32

And this is the schematic right here for the database.

00:35

So you'll see here between order details and orders

00:39

that and products that there is a 1 to 1 and only one relationship. Now what is the 1 to 1 and only relationship?

00:48

Well, if you think back to the employees related to the state that they were in, you could have had

00:54

a John Kate, a Bob that all lived in Arizona. They had a 1 to 1 relationship. They all lived in that one state,

01:02

now 1 to 1, and only one relationship would be like if we said, Well, no, you can't have three employees in one state. They all have to be in different states

01:11

now, in the context of living in the States, that doesn't make too much sense. But where that does make sense is online orders.

01:19

For example, if you place an order online, do some real Taylor. You'll get an order number from that retailer. That order number is yours and only yours.

01:30

Other customers won't have that order number. And that's a good thing. Because if you had a complaint about an order order got mixed up and customers were sharing order numbers,

01:40

it would be mass confusion, and that would be no good for any store.

01:45

So we're gonna take a look at this data base. We're gonna download it and the neat thing about these demo databases, and there's a lot of them out there. You can find him by just searching my sequel demo database, and they usually give you a nice kima. Their pre built their good representation of what you find out there in the wild and, uh, there

02:02

built following the ah, you know, custom design patterns and standards

02:07

that unfortunately are missing from the wild sometimes.

02:13

So if we scroll up here

02:15

when we click the download, my simple sample database

02:17

and click save

02:22

We're gonna Then

02:23

Little Earl Bouncing Mints. It's done.

02:25

Go to your downloads folder,

02:28

right click that

02:29

click Extract All

02:31

open the folder going and double Click that because we affiliated dot sequel to de Beaver. It should start D Beaver with that script open,

02:43

and hopefully

02:45

that doesn't take too long. Shouldn't take too long. Okay, we are good to go

02:49

now.

02:50

If we look do this file that we opened, we'll see that it's a great database.

02:55

Classic models

02:58

used classic models, and then it's gonna create those tables. In addition to creating those tables, it's gonna assert all this nifty fake data forced the query against and makes sense off

03:09

and maybe transform if we're filling. Ah, like having a good time.

03:14

So if you just opened up

03:19

de beber and then have it open, it's possible that this could have said none.

03:23

If it does, make sure you give this a click. You'll get this window. Select a connection, Do you? Ah, establishing the previous lesson and just click Select.

03:32

And once that's done place, you're cursed at the top of the script

03:38

and click the Execute sequel script button

03:42

and you'll see it run through and run. Everything

03:45

all right? That was quick. We're done.

03:46

So the next band, the data basis

03:50

left, clicked on the databases. Then right? Click,

03:53

click, refresh.

03:55

There's our classic models. Now click on that and right click is well and click set as active. That'll make sure that Thea

04:03

the ah, intelligence works correctly where it predicts and shows you what your options are when you're coding

04:11

we're gonna set that is active. Let's take a quick look at the tables.

04:15

Okay, Let's see if that matches the schematic. We were looking at those table names.

04:18

Make sure ah,

04:20

nothing weird is going on.

04:24

Okay, we take a look at this, will see that we got the orders. Order details, products. That's what we're interested in now, when you're right, enquiries. You want to focus on what you're trying to get out of it.

04:33

Every now and then, I see an individual who tries to connect every tables and make one super query that they can just select from. And what that usually results in is miss misunderstood relationships and connections that then skew the results in weird ways. So good

04:50

practice, especially when he knew, is just connected tables that you're interested in getting data out off for that report.

04:58

So we're interested in the products that order details and orders. The order details sits right in the middle,

05:03

So let's give that one a click. Left click, Right. Click view diagram.

05:11

All right, so we see those three same tables, we see the orders, the order details, the products dee beavers being kind to us and saying, Hey,

05:19

this is the keys on these tables or details. Has two keys. Orders has one key

05:25

products has one keep.

05:26

So let's get the quivering click. Sequel Editor,

05:30

new sequel editor.

05:32

And we're gonna type in select Star Star means everything. Select everything. Give me everything from

05:39

orders.

05:41

Okay?

05:43

All right. We're gonna start order. We're gonna work our way up.

05:46

Let's go in and run that and see what happens. Cursor at the top.

05:50

Pretty sure this fails. Let's try.

05:53

Nope, that worked. Okay, I know I've seen that feel before. Maybe they updated in a new version.

05:59

Okay, so that's the data we're getting out right now. That's from one table. That's good. But we need two tables. Let's go ahead and connect. Actually, only three But let's let's work our way up a proper way to format. This is comma because you're listing making a list just like the anglers English language. We use commas,

06:17

order details. That's what we want.

06:23

So if we run this,

06:26

we'll get a bunch of columns,

06:28

which is no good for us right now. So let's be more specific. Let's do orders.

06:32

Not.

06:34

This is where then tell us since comes in very helpful.

06:38

So just see. Ah, let's see, we want the order number

06:41

on. Then let's grab something from order details

06:46

dot

06:47

I'll stay up Coach.

06:50

Well, we need to make a relationship between the tables. Right now, there's no relationship established between the tables, and we'll get wrong results. Usually

06:59

there are a few use cases for not establishing relationship, but that's not the case here. We're gonna take

07:06

for our first constraint

07:09

and with the orders dot order number

07:14

equal

07:15

order details

07:17

dot order number

07:21

that's going to run this. We're selecting the order number.

07:25

The product code

07:27

from orders and order details tables two tables

07:30

where the order number on one table is equal to the order number on another table. Let's give that a run.

07:38

All right,

07:39

so if we look at this, will see that 10 123

07:43

had four products associated with that order number. So we know we're pretty confident. Anyway, when this customer placed this order, he ordered four things.

07:53

Now, let's go ahead and filter out the other order numbers by making an extra constraint.

07:58

We're gonna say where and

08:01

cause once you've used the key word where

08:03

to start

08:05

and then and to continue the constraints. So we're going to say and orders dot order number.

08:11

Oh, that's my orders, right? That's very important.

08:15

Got order number

08:18

equal

08:18

10. 123 No comments here.

08:22

De Beber is kind of Ah, this is one of the things I'm not too fond of. There's probably a way to turn it off, but in the database there is no comma in the data.

08:31

And if we put a common there,

08:33

they won't work.

08:33

See,

08:35

womb failed.

08:37

We'll get rid of that comet will work.

08:41

There we go. So there's those four. We've narrowed it down to justice order number. Let's find out what those products are. And to do that, we need to add one more table. The product stable. So comma

08:52

products.

08:54

There we go.

08:56

Now. We haven't connected

08:58

any relationship. What happens if without a relationship,

09:01

we run the quarry? Well, we're gonna see bad results. Let me show you what I'm talking about.

09:07

Boom. Okay, so what is going on here? Why do we get all these results? Fact that I can tell you right now are incorrect.

09:13

Well, what's going on here is because we haven't defined our relationship.

09:16

My sequel saying, Hey, you've added this table. You having to find a relationship? So you know what? I'm gonna D'oh!

09:24

I'm going to give you a record back for every product that exists in the products table because

09:31

you haven't stated explicitly what you're looking for.

09:35

And that's wrong. In most cases,

09:39

we may see a case where we want to do that, but that's not the case here. So we need to add that relationship to fix this. And I like to keep relationships to the better.

09:48

I'm sorry. I like to keep the relationships together

09:52

and the constraints at the bottom. So what we're gonna do is we're gonna go above the and orders dot Order number equals

09:58

10 1 23 and we're gonna

10:00

add the other relationship that the table has. So we're gonna

10:03

hit, enter right there, type the word. And again

10:07

we're gonna say

10:09

order details,

10:13

detail. Spell it right,

10:16

doc. Product code

10:18

equals

10:20

product.

10:24

That product heard.

10:28

Okay, so now we've established a relationship. Let's look at that skim again.

10:31

We could see that the product code is a key over here. We see the product code is a key. Over here, you can see we have a 1 to 1 and only one relationship again.

10:43

And when we run this query, we should see this clean up quite nicely. So let's give that a run.

10:50

And sure enough, it did it drop back down to the four records that we had before. But we don't have the product name. Let's bring that into the

10:58

into the decree. So it's a comma

11:01

table name

11:03

dot

11:07

There's our product name. Let's give that a click

11:09

and let's play that.

11:11

There we go. Okay, those are cars,

11:15

so this guy must have a fair amount of money because it would appear that within within order 10 123

11:22

this customer purchased four products and those four products were different types of cars. Anyways, this lesson starting to get a little bit long. We're at about 13 minutes.