MySql - concat in query with left outer join

Question

I do not understand. I tried:

SELECT table1.name, CONCAT( country, '.', id ) AS table1.code, table2.name FROM tabel1 LEFT OUTER JOIN table2 ON ( table1.code = table2.code ) 

I need to combine country and id in country.id because table2.code has this scheme.

Thanks in advance.

Answer 1

If I understand you correctly, you may need something like this.

 SELECT t1.name t1_name, t2.name t2_name FROM table1 t1 LEFT JOIN table2 t2 ON CONCAT(t1.country, '.', t1.id ) = t2.code 

SQLFiddle example

Answer 2

For those who pull their hair out due to poor performance ON CONCAT() joins: make sure you put your non-string values ​​on CHAR , this improves performance astronomically:

 SELECT t1.name t1_name, t2.name t2_name FROM table1 t1 LEFT JOIN table2 t2 ON CONCAT(t1.country, '.', CAST(t1.id AS CHAR) ) = t2.code 

The explanation is hidden in MySQL docs for the CONCAT () function :

... If all arguments are non-binary strings, the result is not a binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid this, you can use an explicit cast type ...

The loan for this is Aurimas Mikalauskas .