SQL 2012 How to use Cross Apply Lab 1.2

tables in sequel server can return lots of data to your query there are other objects inside sequel server that can return data also to a query window not all of these objects are tables when you want to join a table to another sequel object that is not a table you have to use a variety of means to get your intended results let's start off by looking at the regular table queries that we're familiar with this one will get all records and all fields from the sales invoice table now looking at the first invoice number from customer 472 how many items did they buy did they stop and buy 10 items did they buy just one did they buy three of the same item and two of another well that level of detail can be found in the sales invoice detail table and we can join the sales invoice table to the sales invoice detail table on the sales invoices invoice ID field equated to the sales invoice details invoice ID field run that and we see sales invoice one has quite a number of products on it sales invoice one product 76 was ordered twice sales invoice one also had products 77 ordered three times it looks like sales invoice one had quite a bounty of products ordered from it sales invoice to add just one sales invoice three just one well let's find out exactly how much this order amounted to now I've got the Product ID but I don't know it's surprise so what we're going to do is another join in or join current products CP on CP dot product ID is equal to SD dot product ID and run it now we are getting every field from all three of these tables which is quite a bit to look at so let's narrow it down from the sales invoice table we want to see the invoice ID from the sales invoice table we want to see the customer ID and we also want to see the order date let's drop down a line and get a few fields from the sales invoice detail table namely the product ID that was ordered and from the sales invoice detailed table we want the quantity now let's drop down another line and from the product table we want some lookup information like product name and retail price okay we can see exactly when things were ordered how many items were ordered and now we can see the retail price and we get the retail price of all the various line items okay this is pretty good now usually when you're producing a detailed report like this you're narrowing down on one particular customer let's look at the customer ID and let's pick one specific customer let's do number 4 it looks like customer four has ordered on two different invoices product 53 on one invoice product 50 on the other how about customer number one Wow 19 rows of information one on this invoice one on this invoice and a whole bunch on invoice number 1401 how about customer ID - not as many items how about customer 3 invoice 443 has four items invoice 828 has two items and one item on 949 and that's a pretty neat report now knowing that this query usually only changes on one particular customer ID pick the one you want here I'm picking customer ID 4 and notice one other change we will now bring in another field that will be an expression field we take the quantity multiply it by the retail price and we get the total line item amount pertaining to that invoice order the time was taken to create what's known as a table valued function this is actually a function that hooks to the combination of tables you just saw and if I wanted to find out what customer one has ordered I put a 1 in here and it produces the report similar to the query you just saw this is known as a table valued function put any of the customer numbers and it will produce their report not only that but as you can see it calculates the total amount spent on that line item of that invoice so the question is how do you join a table valued function to a table this table valued function will produce a detailed report for all the invoices and all the products on those invoices and the amounts for any given customer if I put a two in here we see the results for customer two but it doesn't tell us the name of customer two that can be done with a regular select from a table let's look at our customer table and figure out who customer two is customer two appears to be Lee Jung so how can I join this table to this function well let's start off by trying a regular join I'm going to alias this customer table as Cu and at this point we would attempt to take this other table and do an inner join but what do we inner join on see a function does not support an on clause so I might be tempted to take this Cu and say give me the value from the customer ID field and we run it and we see it doesn't work not with an inner join so what we're going to do is take the customer ID which could be one two three four and pass all of them individually into this function by doing a cross apply and run it and there you get the result well let's take time to order this let's put order by Cu dot customer ID there's customer number one and all of their invoices and order totals here is customer to three customer two is Lee and Lee only ordered one item solely appears only once so the cross supply finds the matching records between a table and a table valued function the same way an inner join finds the matching records between two tables and it allows you to pass in the match inside of the parentheses for the function rather than using a non clause lab 1.2 skill check 1 the function FN underscore get state from location ID returns fields from the state list table for each J proko location ID show the 12 employees who currently work in the J proko locations sort your results by last name and first name when you're done your results should resemble the figure you see here skill check to the function f and underscore get customer trips accepts a customer ID and shows one record for the unique combination of each customers and invoices and products purchased for example if you do select star from FN get customer trips and you pass in customer for it returns the five fields shown below for all of customer force purchases cross apply the customer table with a function FN underscore get customer trips to show the customer name with the trip list alias the customer table is Cu and alias the function as GCT sort your results by customer ID and then invoice ID when you're done your screen should resemble the figure you see here skill check 3 the function FN underscore get customer list accepts a product ID and shows the line of customers who have purchased that product cross apply the current products table with the FN get customer list function to show the product name alias the current products as CP and the function get customer list as GCL when you're done your results should resemble the figure you see here

Channel: Joes2Pros SQL Trainings

Views: 17,269

Rating: 4.8198199 out of 5

Keywords: CrossApply, Cross Apply, SQL 2012, SQL Tutorial, SQL (Programming Language), Software

Id: yqBl2as3ysw

Length: 10min 6sec (606 seconds)

Published: Fri Dec 14 2012