34

I have a table valued function that returns a table. When I try to JOIN the table-valued function with another table I don't get any results, but when I copy the result of the function into an actual table and do the same join, then I get expected results.

The query looks something like this:

Select *
From myTable
INNER JOIN fn_function(@parm1, @param2)
ON ....

All up I have about 4 such queries and each one has slighly different function, but all the functions produce the same table but different data. For some of these queries the INNER JOIN works, but for others it does not.

Any suggesting why this happens?

Anup Agrawal
  • 6,551
  • 1
  • 26
  • 32
user2343837
  • 1,005
  • 5
  • 20
  • 31
  • Could be something related to non-matching datatypes. Can you show us some real code? – dean May 01 '14 at 05:33
  • Sample data and sql fiddle will be useful. – Anup Agrawal May 01 '14 at 05:37
  • give the exact query to help. It should work. I generally give an alias to the function like `INNER JOIN fn_function(@param1, @param2) fn ON....` Exact query will help in finding the solution. – Roopesh May 01 '14 at 13:16

4 Answers4

72

With the table valued function you generally use Cross Apply. 

Select *
From myTable m
CROSS APPLY fn_function(m.field1, m.field2)
Anup Agrawal
  • 6,551
  • 1
  • 26
  • 32
  • 5
    Only if you want to pass something from the outer query into the function as a parameter. Which is not the case for the OP. – dean May 01 '14 at 05:32
  • but this function requires parameters. so do I replace the parameters with the joining columns? – user2343837 May 01 '14 at 05:34
  • Yes if those are same columns. – Anup Agrawal May 01 '14 at 05:39
  • @dean On a sidenote, what do you mean by "OP"? :) – Anup Agrawal May 01 '14 at 06:11
  • 1
    @AnupAgrawal - OP can be used for two closely related terms, in my experience - Original Post (the question that was asked) or Original Poster (the person who asked the question). Usually from context you can deduce which one is meant or it may not even matter which one was meant. – Damien_The_Unbeliever May 01 '14 at 06:17
  • 4
    @AnupAgrawal Cross-applying a function will cause it to be executed once for every row in the outer select, as opposed to joining to it, which executes it only once. You can't just switch the two. And OP stands for 'original poster'. – dean May 01 '14 at 06:17
  • I agree with you @dean. Batch processing in most cases is better than row processing. If the 2 parameters are not coming from some variables and are joining to value from mainTable, in that case I think we have to use Cross Apply. Unless *OP* provides more info, I think we cannot move forward. – Anup Agrawal May 01 '14 at 06:31
  • See [this answer](https://stackoverflow.com/a/5180700/1497596) for more on Cross Apply vs. Inner Join with a Table-Valued Function. – DavidRR Apr 06 '22 at 12:34
1

If we made some assumptions that params of table valued functions are not dependent on myTable columns dynamically this will work.

   SELECT *
    FROM myTable 
    INNER JOIN

    (SELECT * from fn_function(@para1, @para2 etc))
 ON ...

but if the params are dependent on myTable it will not work

Community
  • 1
  • 1
user2343837
  • 1,005
  • 5
  • 20
  • 31
  • This doesn't really answer the question: you just showed a more verbose way to phrase the same result, without explaining how to resolve the question if the parameters *do* depend on each individual row of `myTable`. – underscore_d Nov 28 '17 at 16:12
1

Your "ON" clause of the join is most likely incorrect. Perhaps a small typo like

JOIN x ON oID = odID

instead of

JOIN x ON oID = oID

Vinay Prajapati
  • 7,199
  • 9
  • 45
  • 86
nicko
  • 460
  • 3
  • 11
1

I think this should work

  Select * 
    From Animals 
    Join dbo.AnimalsTypesIds(900343) 
      As AnimalsTypes
      On AnimalsTypes.TypeId = Animals.TypeId

In table valued function the return table should have TypeId so that join works on that clause

Prabhat Sinha
  • 1,500
  • 20
  • 32
Nayas Subramanian
  • 2,269
  • 21
  • 28