MySql - concat in query with left outer join

Question

I don't get it. I've 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 to country.id because table2.code has this schema.

Thanks in advance.

Did you notice that you typed FROM tabel1 rather than table2? — Till Helge
– Till Helge, Commented Mar 9, 2013 at 17:29

peterm · Accepted Answer · 2013-03-09 17:42:50Z

13

If I understand you correct you might 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

answered Mar 9, 2013 at 17:42

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sparky Over a year ago

Your Fiddle link goes to a blank fiddle.

peterm Over a year ago

@Sparky This fiddle was created more 1.5 years ago. It looks like SQLFiddle dropped old fiddles.

Sparky Over a year ago

I realize that. I just thought you'd like to know so you can update your answer to either remove or fix the fiddle.

Robert Saylor Over a year ago

This same method also works in Symfony using Doctrine.

SiliconMind · Accepted Answer · 2015-11-26 20:22:22Z

7

For anyone pulling his hair out because of the poor performance of ON CONCAT() joins: make sure you cast your non string values to CHAR it improves the 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

Explanation hides in MySQL docs for CONCAT() function:

…If all arguments are non-binary strings, the result is a non-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 that, you can use an explicit type cast…

Credit for this goes to Aurimas Mikalauskas.

answered Nov 26, 2015 at 20:22

SiliconMind

2,1774 gold badges27 silver badges49 bronze badges

1 Comment

Fangxing Over a year ago

In case someone wants to set COLLATE: SELECT CAST('test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin

Collectives™ on Stack Overflow

MySql - concat in query with left outer join

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related