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
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…
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
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.