7 hours 36 minutes
I welcome to less than 1.8 the many too many relationship explored and the previous lesson we stole the database that was required to display the relationship. And then the lesson before that. And 1.6, I believe we described the relationship in detail, using some
suitable database tables represented in spreadsheets. Now let's go ahead and open D beaver and take a look at the data base we installed in the previous lesson.
I'm gonna go ahead and bring up the schematic by searching for it again.
So let's see. It was my sequel
sample database employees.
So it's the top hit here at the dev dot my simple dot com domain,
and we're gonna look at the employee structure.
I'm go ahead and take a quick snip of that
so that I can draw on it a little bit.
So if we look at the structure here, this is a many too many relationship facilitated by this table in the center here,
cause we if we were to break this apart, we would see that this relationship
that isn't many too many between the departments
is actually a composition of a one, too many
two from the departments to the department underscore employees table
on the left side. In this picture
and on the right side,
the other side of that composition is of one too many from the employees to the department. Underscore
EMP table and together that creates the many too many relationship,
and this table in the center is often called
a pivot table or a lookup table.
So let's go ahead and bring that up in our database of open D beaver.
Expand the connection we established on a previous lesson.
Expand the databases,
expand the employees database, expand the tables
and then click on the department. Underscore EMP table right click that
and click view diagram.
When you view a diagram in a one in a mini too many relationships,
you usually want to view the diagram of the table in the center because that will give you the entire picture.
If we had viewed the diagram of the department's, we would not have gotten this this part of the schematic that we're interested in
so we could see the employees connects here to the department employees
table and back next to the department stable, just like
we saw on the schematic.
So let's go ahead and connect that up and start writing the sequel that will carry that out.
Before we do that.
Click on your employees database right click it
and set it as your active database so that the intelligence works correctly.
Then click Sequel editor, new sequel editor.
So I'm gonna start with my selection. Now what am I going to select again? I don't know. So I'm just gonna jump down to where I'm going to select from Enlist my tables.
I know I need the employees table.
I need the in fact, let's give that an alias that makes it shorter. And
and I know I need the ah
underscore EMP table And let's call that
And I also know I need the department's table,
and we'll just call that
the E p T.
Now, without any relationship constraints, we would have a Cartesian product. We don't want that.
Let's go ahead and add in the constraints where emp dot
ah hem number
equals d e
dot m number.
So we've connected the employees table
to the department empty table
through the employee number.
So let's add in the
So where D e dot
the e p t
So we've added in the relationship of the pivot table in the center to the apartment number
to the I'm sorry to the department table. So let's go ahead and select the Fields were interested in.
So let's go m dot
on Let's do, um, D p t dot
and let's go ahead and put in the, ah, the numbers for each
object. So imp
And in between here, let's put in the department that
Let's go ahead and select out right now.
Get rid of that.
and we could see a bunch of results that we're getting back.
But is anybody in multiple departments? Well, it's really difficult to tell here, and there's tons of results and it be bad practice to try and look at all those results to try and make a determination on the data structure. So let's
let's do it the right way. Let's add a count variable
after a group,
because if anybody is in multiple departments
after we group them, they should show up with a count greater than one.
So we're going to group by
and we're gonna go ahead and get rid of the department part of it,
because the department would be unique, which would cause the group by statement to fail by making every return result a one.
So when we get rid of that and we grew by
employee number and the play first name, if anyone occurs more than once, we will see that reflected in account.
So let's go ahead and put the count variable there
and see if we get
a anyone greater than one.
All right, so we got some twos.
Let's go ahead and bring out this.
I'm gonna start His name's a little easier. So I think it's pronounced Omar.
We're say, end
Remember, the Kama is not really in the data.
The beaver is just showing that to you so that it's easier to read.
You have to remember to exclude those
because if you put a common the number, it will not work.
So we're gonna go ahead and get rid of our count and get rid of our grouping.
And now we're gonna bring back our previous
elements of the department number in the department name,
and we're gonna go ahead and run this
and we can see that Omar, who is employee 129
is within two departments
production and quality management.
And this, again, is that many too many relationship because we know there's more employees than Omar
So this department belongs to many employees,
and this employee belongs to many departments. And that is our many too many relationships
Well, I hope you enjoy this lesson. I hope you get that query working, and I will see you in the next lesson. Thank you.