CODEWITHSUNDEEP
sqloracle-database
jackie21
jackie21

Reputation: 337

Join on a selected number of digits

The column ddm_zip has only 5 digit zipcodes. The column tt_pad_zip contains values anywhere between 2-5. I would like to perform a join selecting values that appear on both sides of the table.

However, tt_pad_zip has various levels of granularity.

For e.g. when we see value of 55 in tt_pad_zip, it is considering all zipcodes that start with 55, for e.g. 551...553...55636 etc. Hence I want the join to select this.

For e.g. when we see value of 558 in tt_pad_zip, it is considering all zipcodes that start with 558, for e.g. 5581...5585...558743 etc. Hence I want the join to select this.

However if there is a value in tt_pad_zip that has the exact zipcode as ddm_zip e.g. 58636 as shown below, I would like the output to pick this .

column: ddm_zip       column: tt_pad_zip
55636                 55
57254                 5734
58636                 57254
                      58636

Upvotes: 0

Views: 336

Answers (4)

forpas
forpas

Reputation: 164099

You can use conditional join with a CASE statement in the ON clause:

select t1.ddm_zip, t2.tt_pad_zip
from table1 t1 inner join table2 t2
on t1.ddm_zip like case 
  when exists(select 1 from table2 where tt_pad_zip = t1.ddm_zip) then t2.tt_pad_zip  
  else '%' || t2.tt_pad_zip || '%'
end

See the demo.
Results:

> DDM_ZIP | TT_PAD_ZIP
> :------ | :---------
> 55636   | 55        
> 57254   | 57254     
> 58636   | 58636

Upvotes: 2

MT0
MT0

Reputation: 168096

Join on the SUBSTRing of ddm_zip that has equal length to the tt_pad_zip value you are comparing it to. Either:

SUBSTR( ddm_zip, 1, LENGTH( tt_pad_zip ) ) = tt_pad_zip

or:

ddm_zip LIKE tt_pad_zip || '%'

Oracle Setup:

CREATE TABLE table1 ( ddm_zip ) AS
SELECT '55636' FROM DUAL UNION ALL
SELECT '57254' FROM DUAL UNION ALL
SELECT '55824' FROM DUAL;

CREATE TABLE table2 ( tt_pad_zip ) AS
SELECT '55'    FROM DUAL UNION ALL
SELECT '5734'  FROM DUAL UNION ALL
SELECT '57254' FROM DUAL UNION ALL
SELECT '55636' FROM DUAL;

Query:

SELECT *
FROM   table1 t1
       INNER JOIN
       table2 t2
       ON t1.ddm_zip LIKE t2.tt_pad_zip || '%'

Output:

DDM_ZIP | TT_PAD_ZIP
:------ | :---------
55636   | 55        
55636   | 55636     
57254   | 57254     
55824   | 55

Query 2: If you want the most granular match.

SELECT DDM_ZIP,
       MAX( TT_PAD_ZIP ) AS TT_PAD_ZIP
FROM   table1 t1
       INNER JOIN
       table2 t2
       ON t1.ddm_zip LIKE t2.tt_pad_zip || '%'
GROUP BY DDM_ZIP

Output:

DDM_ZIP | TT_PAD_ZIP
:------ | :---------
55636   | 55636     
57254   | 57254     
55824   | 55

db<>fiddle here

Upvotes: 3

Radagast81
Radagast81

Reputation: 3016

I think you are looking for something like the following:

-- Sample Data:
WITH dat (ddm_zip, tt_pad_zip) AS 
  (SELECT 0,55 FROM dual
    UNION ALL 
     SELECT 1,5734 FROM dual
    UNION ALL
     SELECT 2,57254 FROM dual
    UNION ALL  
     SELECT 3,55636 FROM dual
    UNION ALL
     SELECT 55636, 99 FROM dual
    UNION ALL  
     SELECT 57254, 88 FROM dual
    UNION ALL  
     SELECT 57341, 77 FROM dual)
-- Query:
SELECT d2.ddm_zip, d1.tt_pad_zip 
  FROM dat d1
  JOIN dat d2 
   -- Join the data via like:
    ON d2.ddm_zip LIKE d1.tt_pad_zip || '%'
   -- Exclude the data where a "better" match exists:
   AND NOT EXISTS (SELECT 1 FROM dat d3
                    WHERE d2.ddm_zip LIKE d3.tt_pad_zip || '%'
                      AND LENGTH(d1.tt_pad_zip) < LENGTH(d3.tt_pad_zip))

Upvotes: 3

artemis
artemis

Reputation: 7271

You could try using the SUBSTR operation to just compare the first two numbers of each column. I don't know which type of join you want, your table names, or example data, but here is a good syntax.

SELECT
    *

FROM
    table1 INNER JOIN table2 ON SUBSTR(ddm_zip, 1, 2) = SUBSTR(tt_pad_zip, 1,2)

Upvotes: 0

Related Questions