Skip to content
Advertisement

Subselect on multiples values with like operator

How to subselect on multiple values where like operator is included?

Table A
ID   Val
1    I33.1
2    I33.3
3    D10.5
4    X10.1
5    D10.1
6    A12.2

Table B
ID  Val   
1   I33.%%  
2   D10.%%
3   A12.%%

I build Table B with the ‘%’ to get all values from Table A with the like-operator.

I can get the I-values from Table A with

SELECT * FROM TABLE A WHERE VAL LIKE (‘I33.%’)

but i build Table B to go through the whole table with one statement, like

Select * FROM TABLE A WHERE VAL IN/LIKE(SELECT VAL FROM TABLE B)

But this doesnt work. > ERROR: more than one row returned by a subquery used as an expression

Both tables are way bigger, so can i get the all values from Table A with the VAL from Table B?

Desired output

ID  Val
1   I33.1
2   I33.3
3   D10.5
4   D10.1
5   A12.2

Advertisement

Answer

Following query should work in your scenario

SELECT t1.*
FROM TableA t1
INNER JOIN TableB t2 ON t1.val LIKE t2.val

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement