CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-athena
caliGeek
caliGeek

Reputation: 419

Unable to join two tables from two different databases in Athena

I have 2 databases in Athena each with it's own table. I'm not sure how to join two tables.Contractinfo_2019 is a database and so is enrollmentinfo_2019 another database. I keep getting error :

"SYNTAX_ERROR: line 11:10: Table awsdatacatalog.enrollmentinfo_2019.contractinfo2019 does not exist

This query ran against the "enrollmentinfo_2019" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 1bbc3941-4fa1-40a0-87c1-eb093784c990."

    SELECT a.*,
        b.*
FROM 
    (SELECT contract_id,
         plan_id,
         organization_type,
         plan_type,
         organization_name,
        plan_name,
        parent_organization
    FROM contractinfo2019) AS a
LEFT JOIN 
    (SELECT contract_number,
         plan_id,
        state,
        county,
        enrollment
    FROM enrollmentinfo2019) AS b
    ON a.contract_id=b.contract_number
        AND a.plan_id=b.plan_id

Can someone please guide me how to join table's in Athena. I'm not sure what am i doing wrong here?

Upvotes: 7

Views: 12298

Answers (2)

Denise M
Denise M

Reputation: 161

I would recommend re-writing the query using WITH

for example: 

 WITH a AS 

    (SELECT contract_id,
         plan_id,
         organization_type,
         plan_type,
         organization_name,
        plan_name,
        parent_organization
    FROM Contractinfo_2019.contractinfo2019),
b as 
    (SELECT contract_number,
         plan_id,
        state,
        county,
        enrollment
    FROM enrollmentinfo_2019.enrollmentinfo2019) 

SELECT * FROM a
LEFT JOIN b ON a.contract_id=b.contract_number
        AND a.plan_id=b.plan_id

Upvotes: 8

Piotr Findeisen
Piotr Findeisen

Reputation: 20770

You just need qualified table names.

Instead of:

FROM contractinfo2019

use this (assuming I got your database and table name right):

FROM contractinfo_2019.contractinfo2019

Upvotes: 3

Related Questions