CODEWITHSUNDEEP
sqloracle-database
1232141232
1232141232

Reputation: 3

Oracle SQL - How to join a single value to a multi-value column?

I have a table with a multi-value code column and I want to join it to a lookup table which has a single value code. The tables already exist so just a Select statement is ok.

My Query but it only matches columns with only 1 value:

select key, code, desc from <driving table> d 
left join <lookup table> l 
on ',' || d.code || ',' like '%,' || l.code || ',%'

CODE and DESC columns are Varchar

for example:

Driving table:
KEY | CODE 
001   1,2
002   1,2,3
003   2 

Lookup table:
CODE | DESC
 1     desc 1
 2     desc 2
 3     desc 3

Resulting output:
KEY | CODE | DESC
001    1     desc 1
001    2     desc 2
002    1     desc 1
002    2     desc 2
002    3     desc 3
003    2     desc 2

Upvotes: 0

Views: 358

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can quite inefficiently use like for this purpose:

select d.*, l.*
from driving d join
     lookup l
     on ',' || l.id || ',' like '%,' || d.code || ',%';

I would suggest, though, that you work on fixing the data model so you have a table with one row per key and code.

Upvotes: 2

Related Questions