CODEWITHSUNDEEP
mysqljoinsql-like
Alan W
Alan W

Reputation: 301

MySQL JOIN two tables using LIKE and substrings

I have been trying to join two MYSQL tables with columns that contain a common substring.

My first table trim_table is a data set of substrings and its id. Each row contains an id, and a substring such as "1717 WINTON DR".

The second table wp_prop contains a columnfield_313 that may contain the substring found on the first table. An example value of field_313 would be "THE SARGENT III @ 1717 WINTON DR"

The following is the query that I formulated to join both tables:

SELECT *, wp_prop.id as prop_id, trim_table.id as post_id, wp_prop.field_313 
as prop_name, trim_table.trim_value as post_name

FROM 
(SELECT id, TRIM(SUBSTRING(post_title, LOCATE('@', post_title)+1)) as trim_value 
 FROM wp_nd333j_posts WHERE post_type="fplan") as trim_table

JOIN
wp_nd333j_wpl_properties as wp_prop

ON trim_table.trim_value LIKE CONCAT('%', wp_prop.field_313, '%')

Unfortunately, the query does not return any rows. I have decomposed the query into different parts to verify that they work.

It is certain that the substring dataset returns the correct rows by executing the following query:

SELECT * FROM
(SELECT id, TRIM(SUBSTRING(post_title, LOCATE('@', post_title)+1)) as trim_value FROM wp_nd333j_posts WHERE post_type="fplan") as trim_table

I can also verify that my wp_prop table contains a substring similar to the example I posted above by executing the following query:

SELECT * FROM wp_nd333j_wpl_properties as wp_prop WHERE field_313 LIKE "%1717 WINTON DR%"

I also verified that my substring dataset contains my desired substring by executing the query:

SELECT * FROM
(SELECT id, TRIM(SUBSTRING(post_title, LOCATE('@', post_title)+1)) as trim_value FROM wp_nd333j_posts WHERE post_type="fplan") as trim_table 
WHERE trim_value =  "1717 WINTON DR"

Upvotes: 4

Views: 9511

Answers (1)

Alex
Alex

Reputation: 17289

You have just switch your fields in ON clause:

ON  wp_prop.field_313 LIKE CONCAT('%', trim_table.trim_value, '%')

http://sqlfiddle.com/#!9/616a0/1

SELECT  
  trim_table.id as post_id,
  trim_table.trim_value as post_name,
  wp_prop.id as prop_id,  
  wp_prop.field_313 as prop_name
FROM (
  SELECT id, 
      TRIM(SUBSTRING(post_title, LOCATE('@', post_title)+1)) as trim_value 
  FROM wp_nd333j_posts 
  WHERE post_type="fplan"
) as trim_table
INNER JOIN
wp_nd333j_wpl_properties as wp_prop
ON  wp_prop.field_313 LIKE CONCAT('%', trim_table.trim_value, '%')

Upvotes: 4

Related Questions