Joining two Athena tables with two where clauses
18,651
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
Author by
Gaurav Bansal
Updated on June 09, 2022Comments
-
Gaurav Bansal almost 2 years
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
andpt_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'}