Join multiple Tables for View with aggregation

Question

I have the below query that return the records corresponding max time stamp for each src_sys_id.

select distinct s1.src_sys_id, s1.execn_ts, s3.cd_id, s4.appl_nm, CASE WHEN s1.st_mgmnt_cd != "PURGE" THEN 'In-Progress' ELSE 'In-Compliance' END as Status from dlrm_data_retention.ret_st_aud s1 inner join ( SELECT src_sys_id, max(execn_ts) as maxtime FROM dlrm_data_retention.ret_st_aud GROUP BY src_sys_id ) s2 on s2.src_sys_id = s1.src_sys_id and s1.execn_ts = s2.maxtime

How can i join this query with additional tables to get few more columns corresponding to this src_sys_id.

table3 - This one had src_sys_id and cd_id table4 - This has cd_id and src_name.

I need to have the cd_id and src_name columns also as part of my select statement.

I tried the below. It does not return any result.

select distinct s1.src_sys_id, s1.execn_ts, CASE WHEN s1.st_mgmnt_cd != "PURGE" THEN 'In-Progress' ELSE 'In-Compliance' END as Status from dlrm_data_retention.ret_st_aud s1 JOIN table3 s3 JOIN table4 s4 ON s1.src_sys_id = s3.src_sys_id AND s3.cd_id = s4.cd_id
inner join ( SELECT src_sys_id, max(execn_ts) as maxtime FROM dlrm_data_retention.ret_st_aud GROUP BY src_sys_id ) s2 on s2.src_sys_id = s1.src_sys_id and s1.execn_ts = s2.maxtime

ssamuel ssamuel · Accepted Answer · 2018-06-25T13:13:28

Hope this helps..

    select distinct s1.src_sys_id,
    s1.execn_ts,
    CASE WHEN s1.st_mgmnt_cd != "PURGE" 
    THEN 'In-Progress' 
    ELSE 'In-Compliance' 
    END as Status

    from dlrm_data_retention.ret_st_aud As  s1 

    JOIN table3 As s3 ON s3.src_sys_id = s1.src_sys_id 

    JOIN table4 As s4 ON  s4.cd_id = s3.cd_id    

    inner join 
    (
      SELECT src_sys_id, max(execn_ts) as maxtime
      FROM dlrm_data_retention.ret_st_aud 
      GROUP BY src_sys_id 
     ) As s2 on s2.src_sys_id = s1.src_sys_id and s1.execn_ts = s2.maxtime

Join multiple Tables for View with aggregation

1 Answers