CODEWITHSUNDEEP
sqlsql-server
tsionyx
tsionyx

Reputation: 1669

Join a table with results of function for each value of table

I have a table that consists of several Id's like this

SELECT * FROM Objects;

ObjectId
---------
    1
    4
    5

and function that produce another table for each ObjectId, for instance

SELECT * FROM dbo.GetNumbers(1)        SELECT * FROM dbo.GetNumbers(4)       SELECT * FROM dbo.GetNumbers(5) 

NumberId                               NumberId                              NumberId
---------                              ---------                             ---------
    40                                     11                                    12
    45                                     2                                    
                                           18

How can I get the cartesian product of original table with the tables produced by functions without using cursors?

    SELECT ???

    ObjectId    NumberId
    ---------------------
        1           40
        1           45
        4           11
        4           2
        4           18
        5           12

Upvotes: 1

Views: 204

Answers (2)

Jodrell
Jodrell

Reputation: 35706

Don't you just want to do?

SELECT
              o.ObjectId
            , n.NumberId
   FROM
            Objects o
       CROSS APPLY
            dbo.GetNumbers(o.ObjectId) n

If you want to include Objects that have no GetNumbers results use OUTER APPLY.

A simple way of looking at it is, CROSS APPLY is an INNER JOIN to a TVF, OUTER APPLY is a LEFT OUTER JOIN to a TVF.

You shouldn't confuse these with CROSS JOIN which has nothing specific to do with functions and is used to provide the Cartesian Product of two sets, which is not what you want here.

Upvotes: 2

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

Judging by dbo. this might be Sql Server in which case outer apply might help:

select objects.ObjectID,
       numbers.NumberID
  from objects
 outer apply 
 (
   SELECT * 
     FROM dbo.GetNumbers(objects.objectid)
 ) numbers

Upvotes: 2

Related Questions