Joining two Athena tables with two where clauses

Question

I have two Athena tables with the following queries:

select 
  date,
  uid,
  logged_hrs,
  extract(hour from start_time) as hour
from schema.table1
where building = 'MKE'
  and pt_date between date '2019-01-01' and date '2019-01-09'

and

select 
    associate_uid as uid,
    date(substr(fcdate_utc, 1, 10)) as pt_date,
    learning_curve_level
  from tenure.learningcurve 
  where warehouse_id = 'MKE'
    and date(substr(fcdate_utc, 1, 10)) between date '2019-01-01' and date '2019-01-09'

I want to join them on uid and pt_date. How can I do that?

I tried:

select (select 
      date,
      uid,
      logged_hrs,
      extract(hour from start_time) as hour
    from schema.table1
    where building = 'MKE'
      and pt_date between date '2019-01-01' and date '2019-01-09') as a
left join (select 
        associate_uid as uid,
        date(substr(fcdate_utc, 1, 10)) as pt_date,
        learning_curve_level
      from tenure.learningcurve 
      where warehouse_id = 'MKE'
        and date(substr(fcdate_utc, 1, 10)) between date '2019-01-01' and date '2019-01-09'
) as b 
on a.uid=b.uid and a.pt_date = b.pt_date

But the above results in the error mismatched input 'left' expecting {<eof>, ',', 'from', 'where', 'group', 'order', 'having', 'limit', 'union', 'except', 'intersect'}

Answer 1

Syntax for joins in any sql is like below

Select <column list>
  from Table_1
     left/right/inner Join Table_2
     ON <join condition>

table_1 and table_2 can be tables or another select statements

You are missing select * from, try this, I cant check other syntax errors but this is the general idea

select  a.*, b.*
  from  (select  date,
                 uid,
                 logged_hrs,
                 extract(hour from start_time) as hour
           from schema.table1
          where building = 'MKE'
            and pt_date between date '2019-01-01' and date '2019-01-09'
        ) as a
     left join 
       (select  associate_uid as uid,
                date(substr(fcdate_utc, 1, 10)) as pt_date,
                learning_curve_level
          from tenure.learningcurve 
         where warehouse_id = 'MKE'
           and date(substr(fcdate_utc, 1, 10)) between date '2019-01-01' and date '2019-01-09'
        ) as b 
      on a.uid=b.uid and a.pt_date = b.pt_date