졸린 정신으로 답변이 될지.....

간단하게 테이블 변수 이용해봤습니다..

참조 하셔서 활용 해보세요...

아마도 고수님들은 더 쉽게 가능 하실듯...

declare @temp1 table(

id nvarchar(10)

, name nvarchar(30)

)

declare @temp2 table(

id nvarchar(10)

, insert_dt datetime

)

declare @temp3 table(

id nvarchar(10)

, insert_dt datetime

)

insert @temp1

select 'aaa' as id, 'aaa_name' as name union all

select 'bbb' as id, 'bbb_name' as name union all

select 'ccc' as id, 'ccc_name' as name union all

select 'ddd' as id, 'ddd_name' as name union all

select 'eee' as id, 'eee_name' as name

insert @temp2

select 'aaa' as id, '2015-01-01' as insert_dt union all

select 'bbb' as id, null as insert_dt union all

select 'ddd' as id, '2015-01-20' as insert_dt union all

select 'eee' as id, '2015-04-01' as insert_dt union all

select 'aaa' as id, '2015-05-01' as insert_dt union all

select 'eee' as id, '2015-05-10'

insert @temp3

select 'ccc' as id, '2015-01-01' as insert_dt union all

select 'ddd' as id, null as insert_dt union all

select 'ddd' as id, '2015-03-20' as insert_dt

select

a.id, a.name, cc.insert_dt

from @temp1 a

left join (

select bb.id, max(bb.insert_dt) as insert_dt from (

select id, max(insert_dt) as insert_dt from @temp2 group by id union all

select id, max(insert_dt) as insert_dt from @temp3 group by id

) bb

group by bb.id

) cc on a.id = cc.id

테이블 반환 함수도 가능 합니다.

CREATE FUNCTION [DBO].[UDF_JOB_2](@ID VARCHAR (10))

RETURNS TABLE

AS

 RETURN
 (
  SELECT MAX(A.INSERT_DT) AS INSERT_DT
  FROM (
   SELECT MAX(INSERT_DT) AS INSERT_DT
   FROM JOB.DBO.JOB_TBL_2 WITH (NOLOCK)
   WHERE ID = @ID
   
   UNION ALL
   
   SELECT MAX(INSERT_DT) AS INSERT_DT
   FROM JOB.DBO.JOB_TBL_3 WITH (NOLOCK)
   WHERE ID = @ID
  ) AS A
 );

SELECT A.ID, B.INSERT_DT
FROM JOB.DBO.JOB_TBL_1 AS A
CROSS APPLY JOB.DBO.UDF_JOB_2(A.ID) AS B

테이블 1에는 데이터가 무조건 있다는 가정하에

;with table_1 as

(

        select 'a' as ID

), table_2 as

(

        select 'a' as ID, '2015-01-20' AS login_dt UNION ALL

        select 'a' as ID, '2015-01-22' AS login_dt UNION ALL

        select 'a' as ID, '2015-01-23' AS login_dt

), table_3 as

(

        select 'a' as ID, '2015-01-19' AS login_dt UNION ALL

        select 'a' as ID, '2015-01-22' AS login_dt UNION ALL

        select 'a' as ID, '2015-01-24' AS login_dt

)

SELECT ID, MAX(Login_Dt) AS Last_Login_Dt

FROM

(

        select ID, NULL AS Login_Dt FROM table_1

        UNION ALL

        select ID, Login_Dt FROM table_2

        UNION ALL

        select ID, Login_Dt FROM table_3

) AS A

GROUP BY ID

이렇게 UNION ALL로 합쳐서 MAX해도 가능합니다.