INNER JOIN with Table-Valued Function not working

Question

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?

Solution 1

With the table valued function you generally use Cross Apply.

Select *
From myTable m
CROSS APPLY fn_function(m.field1, m.field2)

Solution 2

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

Solution 3

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

Solution 4

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

View more solutions