Question 403265 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to call a (select…) column with a "…" name?

I want to give a (select…) column with a certain name that require double quotation marks, but i want to call this column in a WHERE clause or in a HAVING clause.

Here’s what i’m trying:

select emp_id ID,
concat(fname, ' ', lname) AS "Nome do funcionário",
(select sum(titles.pub_id = E.pub_id) from titles) AS "Número de livros publicados"
    from employee AS E
    where "Número de livros publicados" = 7;

I managed to do it using the HAVING clause, but i had to change "Número de livros publicados" to Total , but I did want the other alias.

select emp_id AS ID,
concat(fname, ' ', lname) AS "Nome do funcionário",
(select sum(titles.pub_id = E.pub_id) from titles) AS Total
    from employee E
    having Total = 7;

What it returns:
enter image description here

Is there a way to do it without changing its alias?

Tags:

3

Answers


  1. 0

    Here’s how I would write the query:

    SELECT E.emp_id AS ID,
  concat(E.fname, ' ', E.lname) AS "Nome do funcionário",
  COUNT(*) AS "Número de livros publicados"
FROM employee AS E
JOIN titles AS T USING (pub_id)
GROUP BY E.emp_id
HAVING "Número de livros publicados" = 7;
    Login or Signup to reply.
  2. 0

    In MySQL, you cannot use aliases directly in the WHERE clause because the WHERE clause is processed before the SELECT clause where aliases are defined. However, you can use aliases in the HAVING clause because it is processed after the SELECT clause.

    To use the alias "Número de livros publicados" without changing it, you can modify your query to use the HAVING clause like this:

    SELECT 
    emp_id AS ID,
    CONCAT(fname, ' ', lname) AS "Nome do funcionário",
    (SELECT SUM(titles.pub_id = E.pub_id) FROM titles) AS "Número de livros publicados"
FROM employee AS E
HAVING "Número de livros publicados" = 7;

    This approach allows you to check the condition on the alias "Número de livros publicados" using the HAVING clause instead of the WHERE clause.

    Source: MySQL Documentation.

    Login or Signup to reply.
  3. 0

    A simple trick would be to make a subquery to select all results and give aliases there, then surround it with outer query, which applies WHERE clause, recognizing the quoted text as a column name of the subquery. It will, however, worsen its performance somehow.

    SELECT *
FROM (
  SELECT E.emp_id AS ID,
    concat(E.fname, ' ', E.lname) AS "Nome do funcionário",
    COUNT(*) AS "Número de livros publicados"
  FROM employee AS E
  JOIN titles AS T USING (pub_id)
  GROUP BY E.emp_id ) AS values
WHERE "Número de livros publicados" = 7;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search