Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

How to join a table-valued Function Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2008 11:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:05 PM
Points: 441, Visits: 933
Matt,

I stand corrected. Thank you.

I have not made the switch to 2K5 and do not plan to, if it is at all possible to jump directly from 2K to 2K8.

Regards
Post #478727
Posted Tuesday, July 19, 2011 5:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 1:09 AM
Points: 8, Visits: 32
/*this behaves like inner join*/

select * from freight_manager
cross apply LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)


/*this behaves like cross join*/

select * from freight_manager
outer apply LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)

Post #1143987
Posted Thursday, September 1, 2011 7:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:39 AM
Points: 1, Visits: 34
Mike answer is perfect answer . When you want to use join between a table and table valued function you can choose either outer apply or cross apply as per your requirement.
Post #1168645
Posted Thursday, September 1, 2011 7:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
lalit.madan (7/19/2011)
/*this behaves like inner join*/

select * from freight_manager
cross apply LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)


/*this behaves like cross join*/

select * from freight_manager
outer apply LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)



I think you will find OUTER APPLY behaves more like LEFT (OUTER) JOIN.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1168649
Posted Wednesday, November 9, 2011 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 2:59 PM
Points: 2, Visits: 21
Hi Guys
Please refer the below link.
http://www.java2s.com/Tutorial/SQLServer/0080__Table-Join/UsingAPPLYtoInvokeaTableValuedFunctionforEachRow.htm

Step 1
---------------------------------------------------------------------
CREATE FUNCTION dbo.fn_getByID(@ID int) RETURNS TABLE
AS
RETURN SELECT first_name FROM employee WHERE ID = @ID
GO
---------------------------------------------------------------------
Step 2
SELECT w.last_name FROM employee w
CROSS APPLY dbo.fn_getByID
(w.ID) AS r
ORDER BY w.ID;
======================================
GO
Post #1203134
Posted Wednesday, November 9, 2011 11:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:54 PM
Points: 13,471, Visits: 12,329
sanajmshaji (11/9/2011)
Hi Guys
Please refer the below link.
http://www.java2s.com/Tutorial/SQLServer/0080__Table-Join/UsingAPPLYtoInvokeaTableValuedFunctionforEachRow.htm

Step 1
---------------------------------------------------------------------
CREATE FUNCTION dbo.fn_getByID(@ID int) RETURNS TABLE
AS
RETURN SELECT first_name FROM employee WHERE ID = @ID
GO
---------------------------------------------------------------------
Step 2
SELECT w.last_name FROM employee w
CROSS APPLY dbo.fn_getByID
(w.ID) AS r
ORDER BY w.ID;
======================================
GO


That won't work in sql2000. There is no CROSS APPLY yet.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1203140
Posted Tuesday, November 29, 2011 4:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 29, 2011 4:33 PM
Points: 1, Visits: 0
Hi
This would also work

SELECT *
FROM dbo.CalculateIncome(p.personid);

Fermön
Post #1213606
Posted Friday, September 14, 2012 9:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 37,101, Visits: 31,653
fermin.js (11/29/2011)
Hi
This would also work

SELECT *
FROM dbo.CalculateIncome(p.personid);

Fermön


I know this post is nearly a year old but ran across it just now. My question is, where does "p.personid" come from in that query?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1359767
Posted Wednesday, April 10, 2013 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 5:36 AM
Points: 1, Visits: 23
Great post. Thank you!
It works like a dream......
Post #1440861
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse