Do a mySQL search for the alias

Question

How is the correct way to search for aliases?

In the code below, I am creating an alias called (USER), within this column I am gathering all the values in the column (DE) and (PARA).

(SELECT *, de as user FROM mensagens WHERE user = '1' GROUP BY projeto ORDER BY data DESC)
    UNION
(SELECT *, para as user  FROM mensagens WHERE user = '1' GROUP BY projeto ORDER BY data DESC)

SQL online: http://sqlfiddle.com/#!9/d506b7/2

`WHERE de= '1'` Aliases are actually created at a later stage in the query process, so you cannot use them there — RiggsFolly, Mar 12 '20 at 14:05
@juergend Ooo can you show a working example in the above case please, for my education. — RiggsFolly, Mar 12 '20 at 14:08
@juergend Thanks, do you want to put the answer for me to mark as done? — Tiago, Mar 12 '20 at 14:12
@RiggsFolly: I could not make it work. Seems aliases are only reusable in the SELECT clause but not in WHERE :-( — juergen d, Mar 12 '20 at 14:16

juergen d · Accepted Answer · 2020-03-12T15:22:57.890

1

You could do

select * from 
(
  SELECT *, de as user FROM mensagens
  UNION
  SELECT *, para FROM mensagens
) tmp
where user = 1
GROUP BY projeto 
ORDER BY data DESC

SQLFiddle demo

Or even simpler:

SELECT * 
FROM mensagens
WHERE 1 in (de, para)
GROUP BY projeto 
ORDER BY data DESC

edited Mar 12 '20 at 15:22

answered Mar 12 '20 at 14:15

juergen d

201,996
37
293
362

Do a mySQL search for the alias

1 Answers1