SQL join uniquely

Question

I have two tables and I want to join them with subqueries. I know that we can do it an easier way, but they want me to do it like this. But my code is not working, and I could no find a way to fix it.

This is my code - can someone tell me what is wrong with it?

select distinct 
    c.region,
    c.data_center,
    c.suite,
    c.pr_date,
    FROM_UNIXTIME(m.siteops_exit_actual) as LROOF
from
    (select 
         region, data_center, suite, pr_date 
     from  
         IDC_DC_SCHED_DATES_SUITES_DEV_FUTURE 
     where  
         ds = '<LATEST_DS:IDC_DC_SCHED_DATES_SUITES_DEV_FUTURE>') c
left outer join
    (select 
         region, datacenter, suite, max(siteops_exit_actual) 
     from 
         suplan_migrations:cea 
     where 
         ds = '<LATEST_DS:suplan_turnups:cea>' 
     group by 
         region, datacenter, suite) m on c.region = m.region 
                                      and c.data_center = m.datacenter 
                                      and c.suite = m.suite

Answer 1

You need to name the aggregation column in the subquery:

select distinct c.region, c.data_center, c.suite, c.pr_date,
      FROM_UNIXTIME(m.siteops_exit_actual) as LROOF
from IDC_DC_SCHED_DATES_SUITES_DEV_FUTURE c left join
     (select region, datacenter,suite, max(siteops_exit_actual) as siteops_exit_actual
      from `suplan_migrations:cea`
      where ds = '<LATEST_DS:suplan_turnups:cea>' 
      group by region, datacenter, suite
     ) m 
     on c.region = m.region and c.data_center = m.datacenter and c.suite = m.suite
where c.ds = '<LATEST_DS:IDC_DC_SCHED_DATES_SUITES_DEV_FUTURE>');

I removed the first subquery as well.