Have two tables t1,t2 from different sources with common data in respective columns t1.c1, t2.c1. The data is mixed alpha numeric.
Running
SELECT t1.c1, t2.c1 from t1.c1 JOIN t2.c1 ON t1.c1 = t2.c1; returns all null for t2.c1.
A query where you select either column on a fixed value (x) will return the correct value. Both columns are set up the same in their respective tables
t1.c1 `Style` varchar(100) COLLATE utf8_bin DEFAULT NULL
t2.c1 `STYLE` varchar(100) COLLATE utf8_bin DEFAULT NULL
So ran a CHAR_LENTH(t(x).c(x)) on both and discovered that the length for a given value is off. One specific result was when both were set to 2307 t1.c1 returned 4 while t2.c1 returned 5. Tried TRIM, CAST, UPPER, LOWER alone and in combinations with no success.
The problem appears to be the STYLE (t2.c1) field Note: c1 not really the right position in actual table;
mysql> SELECT 6_HTS09X.id_6 FROM 6_HTS09X LIMIT 20;
+------+
| id_6 |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
+------+
20 rows in set (0.00 sec)
SELECT 6_HTS09X.id_6,6_HTS09X.STYLE AS 'c.1' FROM 6_HTS09X LIMIT 20;
+------+-----------+
| id_6 | c.1 |
+------+-----------+
| | 1201
| | 1303
| | 1401
| | 2001
| 5 | 2001AW
| 6 | 2001BP
| 7 | 2001GEO
| 8 | 2001HA
| 9 | 2001LA
|10 | 2001LG
| 11 | 2001ORG
|2 | 2001P
|3 | 2001P
|14 | 2001SA
| 15 | 2001STAW
| 16 | 2001SUN
| 17 | 2001VIVA
| | 2007
| 19 | 2007ORG
| | 2065
+------+-----------+
20 rows in set (0.00 sec)
[\CODE]
mysql> SELECT 6_HTS09X.STYLE AS 'c.1' FROM 6_HTS09X LIMIT 20;
+-----------+
| c.1 |
+-----------+
|
|
|
|
|01AW
|01BP
|001GEO
|01HA
|01LA
|01LG
|001ORG
|1P
|1P
|01SA
|2001STAW
|001SUN
|2001VIVA
|
|007ORG
|
+-----------+
20 rows in set (0.00 sec)
Using all other fields in any combination are fine. Only when the c1 field is used is there a problem.
I do have an xlxs file for it, the original source document, which I've tried to clean up but maybe missed something.
How should I proceed?
Besides rubbing a lamp, what should I do?