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)
)
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
)