zeckdude zeckdude - 1 year ago 79
MySQL Question

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

I need to return an alias column

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

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'

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download