Querying Multiple Tables with SQL

hi this is Larry what I want to talk to you today is about how to create simple queries for multiple tables you know I'm just going to show you how we can utilize the syntax you currently understand and be able to do one of two or more different tables in being able to join them together with a common simple syntax this is also known as the implicit syntax for the inner join because what we're doing here is doing a simple inner join between tables meaning that the tables themselves share a common field and so therefore we're going to be joining the table to be able to collect the data based upon the common fields that they have all right so that's what we're gonna begin that's what we're gonna cover today to start out with one of the things you should be understanding is the complexity of the database itself for this example I'm going to be utilizing the the database for the product orders it's a sample one that comes with a system and if you go inside here you can see the different tables right away here we have customers items order details and orders the thing to keep in mind is that even if you have an order it's not one table for an order because you'd have a lot of redundant data you'd have a lot of duplication like that redundancy it also doesn't provide for file independence alright or data independence skills you have finally a paid-up of data independence so what we have is that you're gonna see is that for every order you may have such things as you have the order itself the customers that we're ordering it the details of the order meaning what items were order I mean what specific details there are and then there's the items that it sells the descriptions and stuff that are regarded the items or products in some cases so you can see you may have something for orders may have for just an order you may have four or five or six or seven eight of different tables that you have to use that's why what you're gonna happen up having to do is do a lot of complex more than one table queries alright so if we look at this here if we look into customers we can see what type of columns are outside of here there's the ID which is the P cave it's automatically generated but then you have the names address etc and then inside the orders itself over here is that you have order ID customer ID now do you see any similarity between these two tables at all well the common thing is orders ID here right the customer ID or screening the customer ID excuse me are the same between both of these so for the water table you have a customer ID in there this should be declared as the foreign key meaning that the foreign key it represents the primary key in another table this is where foreign keys come very important but something that you have to learn and understand that 1/4 of the full power of a foreign key alright and so so when you look at this you can see right away these two columns these two tables have 8 shared comb right so therefore we should be able to write a select statement that is going to be able to pull data from both of those tables to create something we want to do so to get started we'd write a new query we just hit the new query button okay and with this new query button we want to select the database we want to use to start in this case we're going to use product orders all right and let's just start a new query here so we want to do is select for this query what we want to do is select and find out for the orders how many where the orders are the order date and the zip code we want to basically itemize this and order it by zip code so we want to know how many things were placed within as order within a zip code and also some gives some date reference so basically we want to find the orders then replaced by zip code all right and then the order date with it so what we're gonna first to do is we're gonna hit select what kind of get over the screen select my Larry select select and we want to do order ID right and we're gonna do order day and we're gonna do we want to know the zip code so that's getting called from the customer table that's gonna be I think it's called causes in okay and then we where do we want to get this from well there are two tables we get this from this is going to be the orders table and the customers table okay and you see what I had ways since I put the two tables in now instead of just having one table since these were all from different tables you're gonna see here I suppose I put the tables in SQL Server the automated checked out checking your code and it says oh it works all right so we've just said that and we've ordered from these now what the big point here is that unless you go in and declare what columns you're going to put in as far as you believe relighting to each other you're gonna get a lot of bad data it's just gonna come out and select all that data from there but you want to do is correlate things to make sure that you're getting the correct numbers from that from that from for the columns okay so we're going to say what you need in here every time you do any time you do an inner join basically what you're gonna be doing is looking for the common columns between there and align them up in a where statement in this case and where's David you can use a joint statement also and joint overall in the long term are good to know and understand and practice because you may have more than an inner joint but inner joints are pretty are pretty much standard and what you're gonna be doing closely and it's good to see it in this manner because most of you probably want to select statements have a long table ready so in this case here we want to join the two tables together so we're going to know we need to do orders where orders dot cust ID right or its orders not or orders dot cust ID right and then we're going to go equals we're gonna go equals to customers God cust ID and so what we've done here is very simply just say select these columns from the orders and customers tables but we're going to match up with a customer ID it was a customer ID in both of them if the warranty has been set up correctly you're going to find that you're going to get the data accurately between these two tables because of that relationship now what I want to also do is just an order by in this case we're going to go order by customer zip and we're also going to day or day just to show you we can do this so we can do it from both tables here in the corner so we have a nice little query here and let's run this right away and see execute this and see what comes up and as you can see here we already have the order of the information coming up pretty quickly and let's go through it there's a lot of orders in air that meet this criteria and if you notice for every customer then like in this customer and the zip code for the zip code excuse me you notice here there's two different orders that were placed okay within the zip code okay and different days so you can pull patterns and data right from this this bunch of data you have you could probably gather some information but if you can see some like this of code is more active than the other one so anyways data provides you to be able to help some information what I want to show you is it can go in there and simply do this with two tables or more using it for dinner joining this is probably the type you're gonna be utilizing more more and more okay more often than that so let's just go in here there and let's take a look at and do it another query with more than two tables okay and let me see I got it saved over here yeah pop it in here so in this case here let's just read through this I'm selecting the order date item the item ID quantity customer first name customer last name so basically I'm looking for the first and last name of people and I wanted to find out what item they order by either an ID without the description and then the quantity of the order so I can get some kind of data on the customer but I'm gonna need to take this from three different tables because you look here we're going to take from the orders table we're going to take from the order details table and we're going from the customers table because the customers table is what has customers first name and last name the orders table is what the guiding point is we want to know where the order date is and then order details such as item ID and the quantity come from the order details so we need to have all three of those people put together and we're going to inter join them so we're going to mention the three and the front tables in there but key here is this for every relationship you need to identify your going to need identify the different types of where the columns are supposed to be connected in this case we know we have to go to order and get the order ID'd equal order ID details in order details we also know we have to go in orders and then detail the customer ID to you with a customers table all right so understand every time every time you want to mo to multiple tables okay you're gonna have to do or where statements that match up the different foreign keys or the tables that are common each other to make this work in this case this last one I'm ordering my customer last thing so let's just say execute this and it comes out on the bottom here and you can see we get this data right here so when you see the customers is Bailey there's Blancas got a lot of orders chaddock's one in Gunther Jacob has a lot to order all right and we can go through here to find this information okay so that's just gives you an example of how to do this for multiple tables with an inner join which is kind of the most common way you're going to be doing things technically if you're not do an inner join you're gonna be looking for other types of joins and relationships aisle they give much more complex and I wanted to show you here how to do with the simple select statement that you've been used to doing

Channel: Larry Domine

Views: 153,237

Rating: 4.6674471 out of 5

Keywords: SQL Server, SQL Server 2012, SQL, query, query multiple tables

Id: 7h9uuILngp0

Length: 10min 39sec (639 seconds)

Published: Sun Sep 25 2016