angel enanod angel enanod - 2 months ago 9
MySQL Question

MySql LEFT JOIN ON (First 6 letters)

Is it possible to

LEFT JOIN
and use only 6 letters in
ON
?

Here's my code.

SELECT
b.UID
FROM master_listing AS a
LEFT JOIN courier_information AS b ON LEFT(a.remarks, 6) = b.courierCode


master_listing table

id remarks
1 112233GOODAY


courier_information table

id courierCode UID
1 112233 27


Unfortunately my query above is not working, it returns NULL.

Is there any way to do this?

Thanks in advance.

Answer

A NULL value would be the expected return for b.anycolumn from the a LEFT JOIN b operation, when there is no "matching" row found in b. (The rows from a will be returned, and MySQL will return NULL values for all columns from b.)

It looks like you are asking why the row from b isn't being matched; why the comparison test isn't returning TRUE for any rows.

What you've omitted is the datatype of the courierCode column.

Given that a query with the condition a.remarks = b.courierCode does find a matching row, and given that we are shown example value in the remarks column as '112233GOODAY', we surmise that the remarks column is character type.

We can also conclude that the comparison is not on character string values.

Evaluated in a numeric context, the string value '112233GOODAY' is going to be interpreted as numeric value, of 112233. So we know that there is an implicit datatype conversion occurring, the remarks is being converted to numeric. That same conversion should be happening on the return from the LEFT() function.

Demonstration setup:

  CREATE TABLE master_listing 
  ( id          INT UNSIGNED PRIMARY KEY
  , remarks     VARCHAR(12) 
  );
  INSERT INTO master_listing (id, remarks)
  VALUES (1,'112233GOODAY');

  CREATE TABLE courier_information 
  ( id          INT UNSIGNED PRIMARY KEY
  , couriercode INT UNSIGNED 
  , uid         INT UNSIGNED 
  );
  INSERT INTO courier_information (id, couriercode, uid)
  VALUES (1,112233,27);

Demonstration query:

  SELECT b.UID
    FROM master_listing a
    LEFT
    JOIN courier_information b 
      ON LEFT(a.remarks, 6) = b.courierCode
  ;

Output as expected:

     UID  
  ------
      27

There's something going on that you haven't revealed. I've made assumptions about the datatypes of the columns.

My guess (just a guess) is that there is a leading space in the remarks column.

For debugging this, output additional columns to inspect the values...

  SELECT a.id
       , a.remarks
       , LEFT(a.remarks,6)
       , HEX(a.remarks)
    FROM master_listing a
   WHERE a.id = 1

If the remarks value has a leading space, then the LEFT(remarks,6) will evaluate to ' 11223', and compared in a numeric context, that is not equal to 112233.