2.4 One to Many Relationships Part 1
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
Already have an account? Sign In »
7 hours 36 minutes
all right, we're gonna module to databases. This is less than 1.5 the one too many relationship
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
in the customer table. We have to records the represent customers, a Kit Parker and a John Smith.
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
that we have three records with the idea of 14 customer I d. 12
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,
but they would need to be unique.
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
the one end on Lee one too many. That's what the three pronged means.
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.
So let's go ahead and get de beaver open.
That shouldn't take too long. Open
on. There we go.
Right. And open that up.
Open our data basis,
but a right click Our classic models that a basic cliques set active.
And we're gonna go ahead and click the sequel, editor and Click New Sequel editor.
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.
So we're gonna start typing select. I don't know what I want yet someone leave a blank
from this is where we list our tables.
order details, comma
Okay. And you could list these tables in any order. The order you list of men does not matter.
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
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
dot order number
P equals order details
dot order number.
And because we only use the key word where once and then we use and
dot product code
equals products, that product code,
and that brings our relationship together. We have these three tables now,
and we just need to attach the customer's table
to take advantage of this one too many relationship we have here.
So let's go ahead and do that. So again, we're gonna do a comma.
Mmm. Again, Until we adds relationship, we have a Cartesian product. We don't want that. So we're gonna
add the relationship here customers that customer number
orders dot customer number.
Okay, so now we have the relationship in
and the thing to remember is you can put these in any order.
And that's ah similar to this math rule where if a equals B
Oh, no, we don't need intelligence. There be then be must equal a
that allows you to rearrange and reorder things. And as long as your relationship says the same thing, you'll get the same results.
So if we will select, we're gonna bring in our customers.
Mmm. Thought customer name.
We're bringing our orders. That order number,
they're gonna bring our order. Details are
are better yet our product
that product name
and then order details again.
20 quantity ordered on then
that price each.
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
times the order details
that quantity ordered.
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.
So to avoid that,
you can place it up here. Quick, Run.
See when we get back here
and that looks like it was exactly what we were
looking for, you can see that we have a customer of ulterior graphic
and you also see single gift stores.
You see the top one until you're a graphic
with order. 10 1 23
a few different products
ordered. For example, if you look at this 1966 Shelby Cobra Sounds like a fancy car
order 50 of those at a price of $43.27 for a total price of 2163
dollars and 50 cents.
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.