Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

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.
06:20
Okay, so that brings us to the close of the one too many relationship lesson. I hope you enjoyed it. And I hope you got that relationship working for you. And I will see you in the next lesson. Thank you.

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