How to get the desired data from 3 tables in Athena SQL query?

user_4914657 0
Viewed 1151

Table 1 (aws_complianceitem) has no primary key and this sample data:

status severity compliancetype title resourceid region
compliant low security 2002 patch i-76765434 ap-south-2
noncompliant high audit 2002 kb patch i-76765434 ap-south-2
compliant medium security 2002 kb patch i-98765434 ap-south-1

Table 2 (aws_instanceinformation) has ipaddress and instanceid as unique keys, and has this sample data:

computername instanceid ipaddress status accountid
SD-SDYH-re22 i-76765434 10.33.23.1 complianed 887878787654
noncompliant i-98765434 10.72.33.1 non-complianed 098776765478

Table 3 (configinstancestate) has ipaddress and resourceid as unique keys and this sample data:

resourceid ipaddress instancestate
i-76765434 10.33.23.1 running
i-98765434 10.72.33.1 stopped

I need the data with all the instanceid's which are running.

This is the desired result:

status instancestate severity title resourceid region ipaddress
compliant running low 2002 patch i-76765434 ap-south-2 10.33.23.1
noncompliant running high 2002 kb patch i-76765434 ap-south-2 10.33.23.1
compliant stopped medium 2002 kb patch i-98765434 ap-south-1 10.72.33.1

tried with the full outer join with below query,

SELECT
    t1.status
    , t1.severity
    , t1.title
    , t1.region
    , t1.resourceid
    , t2.ipaddress
    , t2.computername
    , t2.status
    , t3.instancestate
FROM
    aws_complianceitem                      t1
    FULL OUTER JOIN aws_instanceinformation t2
        ON t1.resourceid = t2.instanceid
    FULL OUTER JOIN configinstancestate     t3
        ON t2.ipaddress = t3.resourceid

but we filter this result which we get as a part of this query with instancestate=blank, has empty records

presto amazon-athena amazon-web-services inner-join sql
1 Answers

It seems like two inner joins - with the relevant conditions - do what you want:

select ac.*, c.*
from aws_complianceitem ac
inner join aws_instanceinformation ai 
    on ai.resourceid = ac.resourceid
inner join configinstancestate c
    on c.resourceid = ac.resourceid and c.ipaddress = ac.ipaddress
user_106767160
Related