Question 200217 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL: where data doesn't match using JOIN, I want it to return values from different column

I have 2 tables that I join using LEFT JOIN, i.e. I take Name from table A and match it to Name from table B. There are some records that do not match and return null values due to it being formatted differently.

Now what I want to do is wherever it isn’t matching, I want to extract the first word in Names from table A and search for it in names in Table B and return those values.

Could someone please help me achieve this? I am giving example below:

Table A:

Names
Allianz games
Beta Test
Car Company

Table B:

Names Type
Allianz games ltd 1
Beta Test 2
Car Company 3

Output I am getting using LEFT JOIN:

A.Names B.Names Type
Allianz games Null Null
Beta Test Beta Test 2
Car Company Car Company 3

Output I require:

A.Names B.Names Type
Allianz games Allianz games ltd 1
Beta Test Beta Test 2
Car Company Car Company 3
Tags:

2

Answers


  1. 0

    You could try two joins – first using an exact match, the second using a ‘LIKE’ match but only if there isn’t an exact match:

    SELECT
  a.Names AS 'A.Names',
  IFNULL(b_exact.Names, b_approx.Names) AS 'B.Names',
  IF(b_exact.Names IS NULL, b_approx.Type, b_exact.Type) AS 'Type'
FROM table_a a
LEFT JOIN table_b b_exact ON
  a.Names = b_exact.Names
LEFT JOIN table_b b_approx ON
  (b_exact.Names IS NULL AND b_approx.Names LIKE CONCAT(a.Names, '%'))

    You’ll need to take care with this, however, because there could potentially be multiple matches in Table B.

    Login or Signup to reply.
  2. 0

    So, you already have a

    SELECT A.Names, B.Names, B.`Type`
FROM A
LEFT JOIN B
ON A.Names = B.Names

    and you successfully find exact matches. You, however want to display results if the second table contains the first. You can get all records like this:

    SELECT A.Names, B.Names, B.`Type`
FROM A
LEFT JOIN B
ON A.Names = B.Names OR B.Names LIKE CONCAT('%', A.Names, '%')

    but this will provide all results and if you want a single result for each A, then you can group by and aggregate, like this:

    SELECT A.Names,
       CASE
           WHEN MAX(A.Names = B.Names) > 0 THEN A.Names
           ELSE MIN(B.Names)
       END AS Names
       MAX(B.`Type`)
FROM A
LEFT JOIN B
ON A.Names = B.Names OR B.Names LIKE CONCAT('%', A.Names, '%')
GROUP BY A.Names

    Finally, if you want to include cases where A.Names contains B.Names, then you can change your join condition to

    ON A.Names = B.Names OR B.Names LIKE CONCAT('%', A.Names, '%') OR A.Names LIKE CONCAT('%', B.Names, '%')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search