2.4 One to Many Relationships Part 1

00:00

all right, we're gonna module to databases. This is less than 1.5 the one too many relationship

00:06

in the one too many relationship. You have one table that has one record that, when related to another table, has many records that connect to that one record. For example, consider a customer table with an order table

00:21

in the customer table. We have to records the represent customers, a Kit Parker and a John Smith.

00:27

Now, if we wanted to know how many orders that kid Parker had placed with some store, we would take his idea of one and travel over to the order table and look for that I d. And we would see

00:41

that we have three records with the idea of 14 customer I d. 12

00:47

three.

00:48

And we see that these records have an idea of 12 and three, which is just a coincidence. These ideas could have been any number,

00:56

but they would need to be unique.

00:58

So if we look at the diagram of the previous database we were working with in a previous lesson, we will see that we have a one too many relationship with the same concept customers

01:08

and orders

01:11

the one end on Lee one too many. That's what the three pronged means.

01:18

And what we're gonna do is we're gonna bring in the customer's table and tie it in to the three previous tables We were working with the products, the order details and the orders.

01:29

So let's go ahead and get de beaver open.

01:33

Mmm,

01:34

That shouldn't take too long. Open

01:38

on. There we go.

01:40

Right. And open that up.

01:42

Open our data basis,

01:44

but a right click Our classic models that a basic cliques set active.

01:49

And we're gonna go ahead and click the sequel, editor and Click New Sequel editor.

01:55

And it saved the query around last time, but it's very easy to rewrite, So we'll go ahead and do that. We need the products, the order details in the orders.

02:05

So we're gonna start typing select. I don't know what I want yet someone leave a blank

02:09

from this is where we list our tables.

02:13

So orders

02:16

order details, comma

02:20

products.

02:23

Okay. And you could list these tables in any order. The order you list of men does not matter.

02:29

And now we're gonna put in our constraints, our relationship in strengths because right now we would get a Cartesian product, which is a duplicate

02:37

record for every record in every table, which would produce a lot of duplicate results for us. So let's go ahead and put where the defining relationships

02:46

so orders

02:47

dot order number

02:51

P equals order details

02:55

dot order number.

02:58

And because we only use the key word where once and then we use and

03:02

order details

03:07

dot product code

03:08

equals products, that product code,

03:15

and that brings our relationship together. We have these three tables now,

03:22

and we just need to attach the customer's table

03:23

to take advantage of this one too many relationship we have here.

03:29

So let's go ahead and do that. So again, we're gonna do a comma.

03:32

Customers

03:36

Mmm. Again, Until we adds relationship, we have a Cartesian product. We don't want that. So we're gonna

03:42

add the relationship here customers that customer number

03:46

equal

03:49

orders dot customer number.

03:52

Okay, so now we have the relationship in

03:54

and the thing to remember is you can put these in any order.

03:57

And that's ah similar to this math rule where if a equals B

04:02

Oh, no, we don't need intelligence. There be then be must equal a

04:09

that allows you to rearrange and reorder things. And as long as your relationship says the same thing, you'll get the same results.

04:17

So if we will select, we're gonna bring in our customers.

04:21

Mmm. Thought customer name.

04:26

We're bringing our orders. That order number,

04:30

they're gonna bring our order. Details are

04:34

are better yet our product

04:36

products,

04:39

that product name

04:42

and then order details again.

04:49

Speller. Right,

04:51

That

04:53

20 quantity ordered on then

04:57

order details,

05:00

order details

05:02

that price each.

05:06

And then let's let's let's sum up the total of that price or get the total that price. So we'll need the order details

05:17

that

05:18

price each

05:19

times the order details

05:25

that quantity ordered.

05:27

Let's go in and run this to see what we get. You want to place your cursor at the front, otherwise, you can run into this air if you end up with your cursor in weird places.

05:35

So to avoid that,

05:36

you can place it up here. Quick, Run.

05:40

See when we get back here

05:43

and that looks like it was exactly what we were

05:46

looking for, you can see that we have a customer of ulterior graphic

05:50

and you also see single gift stores.

05:53

You see the top one until you're a graphic

05:56

with order. 10 1 23

06:00

Ordered

06:02

a few different products

06:05

ordered. For example, if you look at this 1966 Shelby Cobra Sounds like a fancy car

06:11

order 50 of those at a price of $43.27 for a total price of 2163

06:18

dollars and 50 cents.