Joining many tables - SQL Tutorial

- [Instructor] Now that we have identified the fields that we need to respond to the current request from WSDA Music Management, which is what employees are responsible for the 10 highest individual sales. Let's go ahead now and start building out our SQL query that's going to respond to this request. Now, starting with the FROM clause, we can identify the first table that we need which is the Invoice table. We want to alias this Invoice table as i. Next, we want to now join this table to the first table that we need to respond to this inquiry which is the Customer table. So we're now going to put our keyword INNER JOIN and then we specify the Customer table because that's the table we're going to join to. We want to alias this table as c for customer. And now, we are ready for ON keyword, which is going to tell us the exact fields that we're going to be joining the Invoice to the Customer tables. Now, we're going to say i.CustomerId is equal to c, that customer ID. And again, we're using our notation here which is table name, followed by the field from that table. And what we have done here, we've effectively joined our Invoice to our Customer table by way of these two fields. Now, if you recall, we did identify that we needed one more table to add to our current join and that is the Employee table. To do this join, we're just simply going to repeat the INNER JOIN keyword and then include the new table we intend to join, which is now the Employee table. We're going to alias this table as e, and now, we are ready for our ON keyword and to now specify the exact columns that we need to join on. So we are going say, c dot support representative equal to e.EmployeeId. And again, within the Customer table, the support rep does mean the exact same thing as the employee which is in the Employee table. And via these two fields, we are now effectively joining our third table, which is the Employee table. Now, a couple more things to add on to our query here is, first we want to include the ORDER BY and we want to order our results by the invoice total. So we specify the Invoice table with i dot and specify the total and we are going to order our results in descending order. So we include the keyword, DESC. Last but not least, we want to reward the top 10 employees. So let's limit our results to 10. Now, we've done everything that we need to do from our FROM clause. Now, let's include the fields that we want to see displayed. To do this, we include the SELECT above the FROM and now we're going to use our table and column notation to include the various fields, starting with the employee first name. Then we'll do the employee last name. Next, we'll place the employee ID. Next, we'll put the customer's first name, followed by the customer's last name. Then we are going to include the support rep ID which helped the customer. And last but not least, we're going to include the invoice, customer ID as well. And finally, one more field, and that's the total, and we get that from the Invoice table. Now, let's take a look at our complete SQL statement. It's pretty involved here but we are effectively joining our Invoice to our Customer to our Employee table. Then we are ordering our results, limiting it by 10, and we've specified the various fields we want to include in our result. Now, let's go ahead and run our complete statement. Now, we do see, we have a nice result. We have the employees' first names, the customer first names, the support reps who helped them, and the total amount of invoice that that employee generated for the transaction. We can now return to WSDA Music Management and effectively respond to their inquiry which is what employees are responsible for the top 10 highest individual sales. And we can submit this report and effectively respond to this inquiry.