Help with cross apply

I have the following table named Orders

order id          Personid        Order Amount    
1                       1                         300                      

I also have a table named Person
Person ID           Name                    Address1                     City                State      Zip
1                          John Doe               123 Main Street        Houston         TX         77043

I also have a table named OrderAmount

OrderFloor  OrderCeiling    OrderTime    ShipCost    
100                500                    2                     5.75
501                1000                  3                     7.00

I have a user defined table function that returns OrderTime and ShipCost based which floor and ceiling range the order total falls in.  

Using example data, ordertime would be 2 and Shipcost would be 5.75

I am trying to write a query that will return the following
order id          Name                    Address1                     City                State      Zip             OrderTime   ShipCost
1                      John Doe               123 Main Street        Houston        TX          77043            2                  5.75

I know how to write the query to return all but the table that is returned by the function

SELECT orderid, Name, Address1, city, state, zip
                        
FROM orders AS A
            INNER JOIN person as b  ON a.personid = b.personid

Not sure how to get the table data returned by the function.   Perhaps a cross apply????
johnnyg123Asked:
Who is Participating?
 
Anthony PerkinsCommented:
Something like this perhaps:
SELECT  A.orderid,
        b.Name,
        b.Address1,
        b.city,
        b.[state],
        b.zip,
	C.OrderTime,
	C.ShipCost
FROM    orders AS A
        INNER JOIN person AS b ON A.personid = b.personid
	CROSS APPLY dbo.udf_YourFunction(a.OrderAmount) C

Open in new window

0
 
Scott PletcherSenior DBACommented:
If you want to return rows only when there is a matching value from the function, use CROSS APPLY.  If it's even possible the function might not find a match, but you still would want the orders row returned even then, use OUTER APPLY instead.  CROSS APPLY equates to an INNER JOIN, and OUTER APPLY to a LEFT OUTER JOIN.

Btw, the udf table function should be an in-line table function, which is critical for best performance.
0
 
johnnyg123Author Commented:
Thanks Anthony for the syntax

Thanks Scott for the heads up on cross apply vs Outer Apply and suggestion of in-line table function
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.