38

Possible Duplicate:
mysql join query using like?

I want to do a join where one column contains a string from another table's column:

SELECT
a.first_name,
b.age
FROM names a
JOIN ages b
ON b.full_name LIKE '%a.first_name%'

Is this possible? I'm using MySQL. Of course the above query will not work since the LIKE '%a.first_name%' will just look for the string a.first_name, and not the column's actual value.

Community
  • 1
  • 1
Don P
  • 60,113
  • 114
  • 300
  • 432
  • If you have a % at the beginning of the string, it will not be able to use whatever indexes you have on that b.full_name column. Just letting you know that your performance will be terrible if you have a sizable database here. – Jordan Feb 04 '13 at 22:23
  • You'll get what you ask for though. so if first name is Jo, you'll get josephine, josiline, jobob, jody, joseph etc.. – xQbert Feb 04 '13 at 22:33

2 Answers2

61

You only need to concatenate the strings, you could also do a search and replace.

SELECT
    a.first_name,
    b.age
FROM names a
JOIN ages b
ON b.full_name LIKE '%' + a.first_name + '%'
colin-higgins
  • 1,087
  • 10
  • 14
30

You can use CONCAT:

SELECT
  a.first_name,
  b.age
FROM
  names a JOIN ages b
    ON b.full_name LIKE CONCAT('%', a.first_name, '%')

or also LOCATE, that returns the position of the first occurrence of a.first_name in b.full_name:

SELECT
  a.first_name,
  b.age
FROM
  names a JOIN ages b
  ON LOCATE(a.first_name, b.full_name)

if there's a match, the join will succeed.

fthiella
  • 48,073
  • 15
  • 90
  • 106
  • 2
    LOCATE() is interesting, will that just return a non-zero value when the string exists in b.full_name, and return false otherwise? – Don P Feb 04 '13 at 22:26
  • 1
    @DonnyP locate will return 0 if there's no match, or the position of the first occurrence of first name. 0 is considered equivalent to False, if value is >=1 it is considered True – fthiella Feb 04 '13 at 22:29