I have two table which contains two different primary key, lets call them, table1 and table2.
The tables may have the same number of columns.
Table1:
| ID | NOM | CODE |
|---|---|---|
| 1 | AAA | 661YYYDD |
| 2 | BBB | YYYD661 |
| 3 | CCC | YD661 |
| 4 | DDD | P5500Z |
Table 2:
| ID | KEYCODE |
|---|---|
| 1 | 661 |
| 2 | 55 |
I want to be able to get by KEYCODE: ALL record in table1 which contain 661 or 55. For example when I select by 661 I get only the first 3 rows from tables1.
CodePudding user response：
This works as well:
SELECT *
FROM TABLE1
JOIN TABLE2
ON TABLE1.CODE LIKE '%'||TABLE2.KEYCODE||'%'
WHERE TABLE2.KEYCODE = '661'
CodePudding user response：
Here is one option:
for every keycode from table2 select a row from table1 that has that number in column code but it must be the first number for example if you have a value like 'XX123XX661' that row wont be selected because the query will compare 661 from table2 to 123 not 661 which is second number in the same string.
select * from table1
where to_number(regexp_substr(code,'[0-9]{1,}'),'999999') in (select keycode from table2)