How can I use an alias for a CONCAT in MySQL?

Question

I need to return an alias column eventTitle of which the content can be variable depending on whether another column's value is null. I also need to use the value of that alias to concatenate some other values to return another alias title.

I am running the following query:

$sql = 'SELECT CASE WHEN session.title IS NOT NULL THEN session.title ELSE course.title END AS eventTitle, CONCAT(eventTitle, "\n", session.city, ", ", session.state) AS title, FROM session LEFT JOIN course ON course.id = session.course_id';

I get the following error:

Unknown column 'eventTitle' in 'field list'

Barmar · Accepted Answer · 2016-10-18 20:08:08Z

You can't refer to column aliases in the same SELECT clause. Put it into a subquery. Also, instead of CASE you can use IFNULL (or the more standard COALESCE).

SELECT eventTitle, CONCAT(eventTitle, "\n", city, ", ", state) AS title FROM (SELECT IFNULL(session.title, course.title) AS eventTitle, session.city, session.state FROM session LEFT JOIN course ON course.id = session.course_id) AS subquery

Or you could just use IFNULL twice in the same query.

SELECT IFNULL(session.title, course.title) AS eventTitle, CONCAT(IFNULL(session.title, course.title), "\n", session.city, ", ", session.state) AS title FROM session LEFT JOIN course ON course.id = session.course_id

BTW, I think you either have the null check or LEFT JOIN backwards. When you use LEFT JOIN, you get NULL in the columns from the second table when there's no match. So the column that will be NULL would be course.title, not session.title.

thanks for the feedback. Unfortunately when I try to run that I get Unknown column 'session.city' in 'field list'
Sorry, fixed it. Need to return those columns from the subquery.
Yup, that definitely worked. I think the second approach is easier to follow. thanks!
Yeah, I typically use the first method when the calculation is really complicated. If there were no IFNULL function, so I had to write the CASE expression, I would do it.

Collectives™ on Stack Overflow

How can I use an alias for a CONCAT in MySQL?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related