sql - MySQL join doesn't return results even when it should -
I have a seemingly straight forward problem. I am matching a list of IDs with the second list of IDs in the second table. The following queries perform properly and return the results:
Select from data * ID = 3888; * Select * with more ID where mmid = 3888;The statement to join, however, does not give any returns.
Select from data> Connect to internal data moreIDs data.table = moreIDs.mmID;Any idea why I am not getting any result from being involved in it?
As you have completed the comments, your problem is related to the data type.
The following fleet shows some tests:
First of all, I have created three tables as the following:
Make Table Table 1 (id varchar (15)); Create table table 2 (id varchar (15)); Create table table 3 (id int);
And put some data:
Include table 1 values ('3888'); Include table 2 values ('3888'); - Include additional spaces in table 3 values (3888);
If you compare the varchar
column to a int
value, then varchar
> Implications will be put on int
and extra space will be removed. The following examples return 3888
:
SELECT * to Table 1 WHERE id = 3888; SELECT 2 TO 2 WHERE ID = 3888;
But if you try this match in the join
operation, then you varchar
to varchar's
, therefore, '3888' = '3888'
will be evaluated as false.
To resolve this, you use one of the columns int
(hence the cast will be used) or use the TRIM ()
function , Such as:
SELECT * to Table 1 Join In Table 2 INNER Trim (table1.id) = TRIM (Table 2. ID);
Note: If possible, change the both columns to int
to get queries
varchar
to int
).