Join with like and prevent multiple results-CodePudding

I want to join 2 tables and search value with like (only first 4 strings will be compared) in join. But the result should be only 1:1, I want to prevent multiple results for 1 entry in the main table.

tab1

name
jackson
michael

tab2

name	code
JACK	12345
JACK X	67890
Micha	12000
Michael T.	90000

Result

name_tab1	code_tab2
jackson	12345
michael	12000

I get more than one result for each entry in tab1.

Select * from tab1
left outer join tab2 on lower(substr(tab1.name,1,4) like '%'||substr(trim(lower(tab2.name)),1,4)||'%'

CodePudding user response：

If you don't mind what code to take, you can group by name and take max/min of code. Something like:

select tab1.name, max(tab2.code) 
from tab1
left outer join tab2 on lower(substr(tab1.name,1,4) like '%'||substr(trim(lower(tab2.name)),1,4)||'%'
group by tab1.name

CodePudding user response：

Try this:

WITH 
  TAB1 (NAME) AS 
(
VALUES
  'jackson'
, 'michael'
, 'Jack Daniels'
, 'Michelin'
)
, TAB2 (NAME, CODE) AS 
(
VALUES
  ('JACK',       '12345')
, ('JACK X',     '67890')
, ('Micha',      '12000')
, ('Michael T.', '90000')
)
SELECT A.NAME, B.CODE
FROM TAB1 A
LEFT JOIN TABLE
(
SELECT B.CODE
FROM TAB2 B
WHERE 
  LOWER (SUBSTR (A.NAME, 1, 4)) 
  LIKE '%' || LOWER (SUBSTR (B.NAME, 1, 4)) || '%'
FETCH FIRST 1 ROW ONLY
) B ON 1 = 1

NAME	CODE
jackson	12345
michael	12000
Jack Daniels	12345
Michelin	12000

dbfiddle link