MySQL Left Join Concat Table Name

perpetualshaun · Jan 31, 2014

I'm fairly sure there should be a way to do this, but I've tried several different things in the past hour, and still no joy. Here's my query:

SELECT
	v. * ,
	s.questionnaire_abbr, s.questionnaire_organization, s.questionnaire_description,
	u.*
FROM
	user_caller_values v
LEFT JOIN
	questionnaires s ON s.questionnaire_id = v.cv_survey_id
LEFT JOIN
	CONCAT(s.questionnaire_abbr,"_users") u ON u.response_pcode=v.cv_user_pcode
WHERE
	cv_admin_id =8
		AND
	cv_key =  'user_next_call'
		AND
	cv_value BETWEEN 1391144400 AND 1391230799 
		AND
	cv_survey_id =430

If the questionnaire_abbr field (from the questionnaires table) is title test, there is another table out there title test_users that I'm trying to pull in with the last join. But when I run this query through phpMyAdmin, I get this error:

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT( s . questionnaire_abbr , "_users" ) u ON u . response_pcode = v . cv_use' at line 1

We're running MySQL 5.0.95 on our server.

Any ideas?

Weedpacket · Jan 31, 2014

You can't use CONCAT or similar functions on identifiers, only values. In fact, if you're trying to then you're doing something wrong (in this case, you're trying to store data in the name of the table, instead of in the table itself).