SQL Inner Join Multiple Tables with SUM Tutorial - SQL 2008/2012/2016/2017

a sequel joins answer important questions that can be pulled out of the data stored in multiple sequel tables in this demonstration these are some of the sales questions we will be able to answer by joining four tables together using the sequel joint statement and some aggregate functions such as the sum function information is broken down into fragments of data called tables inside sequel server it's necessary to join the fragments back together in order to get the complete picture of what took place you can see our other videos on normalization to understand how these tables get broken down this video will present a simple example of a sales database if you wish to try the examples in this video please download the script from our website check the video description for a link to the file here's the zip file after the download double-click the zip file and you'll see inside of it is a dot sequel script drag that script out and save it somewhere and now all you have to do is double click the script and it will open up inside your sequel server notice it refers to a database called bid Wix joins let's create that database right click on databases and choose new database all you need to do is enter the database name and click OK then click on the script and execute when you go into the bid Wix joins database you'll now see there are four tables let's create a quick database diagram if your database needs the objects in order to create diagrams go ahead and click yes select all four tables and add them to the diagram and then click close you can see from this diagram that all the tables have a primary key defined and some of the tables have foreign key relationships between them these foreign key relationships prevent one table from containing data that's not in another table for instance I can't have a sale without a valid customer let's have a look and what's inside the tables so I'll create a new query make sure it's using the correct database bid Wix joins and I'm going to select from all the different tables so my database has four customers and each of them has an ID for products each of them has a unique ID then there is a sale table that matches a sale to a customer and for each of those sale IDs you'll notice it can exist multiple times in the sale detail table because each customer on each sale could buy different products for instance here I see sale ID one but sale ID one has two different sale detail IDs because two different products were sold and again remember you have to go to the sale table to find out which customer bought the two products in sale ID one so let's talk about the inner join the inner joins characteristics are described like this you have a population of all your customers as represented by the customers table and you have a population of all your sales which are represented in the sales table but that doesn't mean that all customers all had sales during March 2013 an inner join would join customers to sales only where customers had sales and the green intersection between customers and sales shows that group of customers who had sales in March 2013 let's create a simple inner join to demonstrate this point so right now I'm only interested in customers and sales so I'm going to comment out the lines on product and sale detail and execute now you can see from just looking at the data based on customer ID where I have one two three and four and when I look at customer ID down here I see one two and four I don't see three we'll come back and we'll talk about that later for the inner join purposes what I want to know is what customers indeed did have sales and in fact I'm going to add a where clause to narrow that down to which customers had sales in the month of March let's make some room here let me comment out these other two rows and let's create our join so what I want to know is the customer customer ID from the customer table and I'm going to inner join that the sale table on the field which they have in common which happens to be customer ID notice the equal sign it's going to become important when we get to do outer joins because you have a left hand side and the right hand side of the equal sign in an inner join it's not so important what's important in an inner join is that these two fields are in both tables and they are the fields which those tables have in common now let's run this query you can see from the result sets I've returned all the customer IDs which had sales customer ID 3 is not in the list in fact let's add our where clause you so my where Klaus is now asking to join sales and customers inner join them so that I only have customers that did have sales on the fields they have in common where the order date is between the 1st of March and the last day of March and now I've narrowed it down to the four sales that I had in the month of March if I only want to see unique customer IDs I can come up here to my select and say select distinct customer customer ID and execute that and there they are the three customers that had sales in the month of March since you're probably going to come across this let me show you a shorthand that's often used in joins this shorthand allows you to alias the tables so that you don't have to repeatedly type customer customer customer where you want to use the customer table so right here after the from clause where I first identify the customer table I can alias that with a C and over here where I joined sale I can alias it with an S and then any time I want to refer to the customer table I can just call it C and any time I want to refer to the sale table I can call it s and you'll notice when I run this query I get the same results a lot of times you'll see people using a and B or sometimes as I have done they'll use letters that somehow represent the table that the alias is pointing to another shorthand that you will see is the fact that an inner join doesn't really need the word inner so I'll delete that run my query and I get the same results the reason for that is because outer joins always have to be specified as left or right so anytime the word join is used by itself sequel will assume it's just an inner join so as I've mentioned and let me straighten this out here here's our joint clause I've joined customer and called it C to sale and I've called it s on the field they have in common customer ID now I'm going to add a second join by joining the sale detail table and I will alias that s D on the field that it has in common which is sales sale ID since I'm going to want a complete report of sales I'm going to get rid of the distinct statement and the magic of joining multiple tables is that you can now in your select statement select fields from any of the tables in your join for instance maybe I'm not interested in the customer ID maybe I want to see the see first name the customer last name the sale order date and the sale detail product ID again since I'm only using inner joins I'm only getting sales where there actually were customers so we've joined three of our tables together now let's join the fourth one this will allow us to report on any of the details from any of the tables again you can only join tables that have common fields so now I'm going to join the last table product which I'll Elias with a P and I'll join it on the field that has in common which is product ID and it has that field in common with the sale detail and now that I've joined product I no longer have to look at the product I be from the sale detail I can actually view the product name I can also view the quantity from the sale detail table so as you can see my query is pulling details from every single table that I've joined together I can even add calculated values that use fields from two different tables such as SD quantity times product price which I will alias as total price so now we have a result set that shows throughout the month of March every single time every customer bought a product what the quantity they purchased was and the total price for that individual line with a few minor changes to this query we can actually turn it into a report that shows by customer how much the total amount they spent per product in the month of March was we'll use the sum function to accomplish this let's take out the order date and sum the quantity times price we also won't need quantity and we'll group our results according to the fields in our select statement the total report now shows how much was spent by customer by product in the month of March you please remember to subscribe

Channel: Edward Kench

Views: 210,078

Rating: 4.8473825 out of 5

Keywords: sql server, join, inner join, outer join, sum, aggregate, count, max, min, data, SQL (Programming Language), sql 2012, sql 2008, query, Technology, tutorial, training, Software, database programming, queries, sql sum, multiple tables

Id: NuJn9TVCuK0

Length: 18min 3sec (1083 seconds)

Published: Sun Mar 24 2013