I have Table2:

Table1:

Table2 has many more columns than Table1. The actual Table1 is larger, but I made it 3 rows for the sake of the example.

I want the following result:

The ZIP2 column is the 9 digit zip code with the first five numbers being the ones I'm interested in.

I tried the following code (with the help of another answer):

SELECT *
FROM Table1 t2
LEFT JOIN Table2 t1
ON substr(t1.ZIP, -5)=t2.ZIP1
    OR substr(t1.ZIP,-5)=substr(t2.ZIP2,5)

My issue with this code is that it has more rows than my original Table1, which shouldn't happen. The result should have the same amount of rows as Table1. Where am I going wrong here?

CodePudding user response：

You may join using both SUBSTR and INSTR:

SELECT t1.ID, t1.ZIP1, t1.ZIP2, t2.ZIP, t2.INCOME
FROM Table1 t1
LEFT JOIN Table2 t2
    ON SUBSTR(t2.ZIP, INSTR(t2.ZIP, ' ')   1, 5) =
       COALESCE(t1.ZIP1, SUBSTR(t1.ZIP2, 1, 5));