CODEWITHSUNDEEP
sql-servert-sqlfunctionsql-server-2005join
davioooh
davioooh

Reputation: 24676

How to join this two functions result sets?

I have two functions:

fn_get_AB_associations(Date from, Date to, Int AentityId)

Getting result set with these fields:

datefrom | AentityId | BentityId

And

fn_get_BC_associations(Date from, Date to, Int BentityId)

Getting result set with these fields:

datefrom | BentityId | CentityId

I need to select all C entities associated with A entity, in the Date range.

I was trying to do something like:

select DISTINCT T1.BentityId from dbo.fn_get_AB_associations('2013-04-01', '2013-04-15', 'PF')  T1
INNER JOIN fn_get_BC_associations('2013-04-01', '2013-04-15', T1.BentityId)  T2
ON T1.BentityId = T2.BentityId

But I obviously get this error: The multi-part identifier "T1.BentityId" could not be bound.

So... is there a way to join this two result sets or I have to cicle results of the first function and call second functiono for each one?

Upvotes: 0

Views: 88

Answers (1)

Devart
Devart

Reputation: 121912

Try this one -

DECLARE 
      @DateStart DATETIME
    , @DateEnd DATETIME

SELECT    
      @DateStart = '2013-04-01'
    , @DateEnd = '2013-04-15'

SELECT DISTINCT 
      t1.DateFrom
    , t1.AentityId
    , t1.BentityId
    ,  t2.* 
FROM dbo.fn_get_AB_associations(@DateStart, @DateEnd, 'PF') t1
OUTER APPLY (
    SELECT 
          t2.DateFrom
        , t2.CentityId
    FROM dbo.fn_get_BC_associations(@DateStart, @DateEnd, t1.BentityId) t2
    WHERE t1.BentityId = t2.BentityId
) t2

Upvotes: 2

Related Questions