sqlamazon-athena

Amazon Athena: SQL Query for Column values with different character legnths


I have two tables an "Enrolled Table" and a "Customer Table".
I only want to show all records on the Customer Table where the enrolled_no on the Customer table matches the enrolled_no on the "Enrolled Table".

Select all the records from the Customer Table by matching the enrolled_no column from both tables.

Assumptions: On the Customer Table, the enrolled_no column is either showing the last 7-characters or 8-character in length of the enrolled_no from the Enrolled Table

The issue I have is on the Customer Table, because the column enrolled_no shows the last 7-characters or last 8-characters in length, how do I write a query that matches the enrolled_no from the Customer table to the Enrolled Table.

Enrolled Table

customer_id enrolled_no
cust1 123a233e0000001
cust2 323a262e0000002
cust3 854a222e0000003

Customer Table

customer_id product enrolled_no
cust1 product1 e0000001
cust2 product2 0000002
cust3 product3 0000003
cust4 product4 na

Desired output

customer_id product enrolled_no
cust1 product1 e0000001
cust2 product2 0000002
cust3 product3 0000003

I have tried the below, but wanted to check if there is a better way to write the query

with enrolled_table AS (  

SELECT  
customer_id,  
SUBSTR(enrolled_no,-8) AS enrolled8,  
SUBSTR(enrolled_no,-7) AS enrolled7,  
FROM enrolled  
)  
SELECT*  
FROM customer c  
WHERE (  
c.enrolled_no in ( select enrolled7 from enrolled )           
or  
c.enrolled_no in ( select enrolled8 from enrolled)    
)

Solution

  • Try

    SELECT c.customer_id,c.product, c.enrolled_no  
    FROM customer c  
    inner join enrolled e 
      on SUBSTR(e.enrolled_no,-8)=c.enrolled_no
       or SUBSTR(e.enrolled_no,-7)=c.enrolled_no
    

    Or

    SELECT c.customer_id,c.product, c.enrolled_no  
    FROM customer c  
    inner join enrolled e 
      on SUBSTR(e.enrolled_no,-length(c.enrolled_no))=c.enrolled_no
    

    Or

    SELECT c.customer_id,c.product, c.enrolled_no  
    FROM customer c  
      where exists
         (select 1 from enrolled e 
          where  SUBSTR(e.enrolled_no,-length(c.enrolled_no))=c.enrolled_no
         )