Join on concat

How do I join on a concatenated column?

This code throws this error: Unknown column ‘test.var’ in ‘on clause’

My code:
SELECT
id,
CONCAT_WS(’ ',dir,sn) AS var
FROM
test
JOIN
test2
ON
test.var = test2.dir_sn

replace the test.var on the join with the entire CONCAT portion. The alias only works on the select.

SELECT id
     , CONCAT_WS(' ',dir,sn) AS var
  FROM test 
  JOIN test2 ON CONCAT_WS(' ',dir,sn) = test2.dir_sn

NOTE: You may need to include the table on the CONCAT_WS, but I’m not 100% sure of that. Try…

1 Like

Very cool. Thank-you very much.

Do you know whether the concatenation, in the JOIN, is automatically indexed?

No, it wouldn’t be.

another approach –

SELECT vw.id , vw.var FROM ( SELECT id , CONCAT_WS(' ',dir,sn) AS var FROM test ) AS vw INNER JOIN test2 ON test2.dir_sn = vw.var

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.