Search code examples
mysqljoinsqldatatypes

mysql db not recognizing valid join

I'm not quite sure what the problem is here, I just know that the db isn't working properly.

Two show some basic data structure, table 1 looks like this

      name         | inGameID
Azure Plagioclase  |  17455
   Plagioclase     |  18
  viscous Pyroxeres|  17460
      Onyx Ochre   |  1232

table 2 resembles something like this

  userName  |    item
     sam    |   Onyx Ochre
    mike    |   viscous Pyroxeres
     dave   |    azure plagioclase

When attempting to run this query

SELECT fl.item, od.name 
FROM oreData od
JOIN fleetLog fl ON lower(od.name) = lower(fl.item)
order by fl.logID asc

The result is ONLY the onyx ochre value. When there should be far more data returned. Both t1.name and t2.item have the same data type. I'm not sure what more detail is needed to help diagnose this problem, but I'd love an extra set of eyes on this. I'm happy to give any further details. I do apologize for this being so vague, I've just never encountered an issue like this before

you guys hit the nail right on the head. it was extra white space either at the front or end of those values. What would be the best way to solve this?

'39';'2';'1';'2001-03-13 00:14:27';'userName';'322';'Vivid Hemorphite
'
'38';'2';'1';'2001-03-13 00:19:28';'userName';'6386';'Azure Plagioclase
'
Solution

  • you guys hit the nail right on the head. it was extra white space either at the front or end of those values. What would be the best way to solve this?

    1. Fix your application code so that such data does not enter the database; if this is not possible, you can define triggers within the database to strip characters as required during write operations:

      CREATE TRIGGER oreData_ins  BEFORE INSERT ON oreData  FOR EACH ROW
SET NEW.name = TRIM(NEW.name);

CREATE TRIGGER oreData_upd  BEFORE UPDATE ON oreData  FOR EACH ROW
SET NEW.name = TRIM(NEW.name);

CREATE TRIGGER fleetLog_ins BEFORE INSERT ON fleetLog FOR EACH ROW
SET NEW.item = TRIM(NEW.item);

CREATE TRIGGER fleetLog_upd BEFORE UPDATE ON fleetLog FOR EACH ROW
SET NEW.item = TRIM(NEW.item);

    2. Update the existing data:

      UPDATE oreData  SET name = TRIM(name);
UPDATE fleetLog SET item = TRIM(item);