I am trying to join 2 tables based on a field value in the first table that is a complete value, and 'like'ing to the variable contents of another field in the second table...

e.g.

Table A contains 2 columns as NAME = 'Bob' and JOB = 'Builder'
Table B contains 2 columns as REVIEW = 'Good' and TITLE = 'This Builder is recommended'

my query would be...

SELECT A.NAME, B.REVIEW
FROM A
INNER JOIN B
ON A.JOB LIKE %(B.TITLE)%

My question is how to format the second part of the LIKE clause as i want to build the wildcards around the results of the results of the db field rather than a fixed value.

I have tried " LIKE '%' + B.TITLE + '%' as recommended in another question but this is returning nothing.

Thanks for any advice

1 Answers

1
forpas On

It is the other way around and you need to concatenate the wildcard chars and the column value:

SELECT A.NAME, B.REVIEW
FROM A
INNER JOIN B
ON B.TITLE LIKE concat('%', A.JOB, '%')