Link to home
Start Free TrialLog in
Avatar of johnnyg123
johnnyg123Flag for United States of America

asked on

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????
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of johnnyg123

ASKER

Thanks Anthony for the syntax

Thanks Scott for the heads up on cross apply vs Outer Apply and suggestion of in-line table function