Geek Answers Handbook

Does the column not exist?

I wonder if anyone can help me with this question:

SELECT u1.id,count(DISTINCT u2.userstatus) as TEMPCOLUMN FROM users AS u1
JOIN friendssym ON u1.id = friendssym.user_id
JOIN (SELECT * FROM users) as u2 ON friendssym.friend_id=u2.id
WHERE TEMPCOLUMN=1 
group by u1.id;

I want only results in which the counter (which is renamed) is 1. I get an error message with this request:

 ERROR:  column "tempcolumn" does not exist

But the column must exist, right? Can anyone help? Thank!

+5
source share
1 answer

You cannot reference a column alias in a WHERE clause.

  SELECT u1.id,
         COUNT(DISTINCT u2.userstatus) as TEMPCOLUMN 
    FROM USERS AS u1
    JOIN friendssym ON u1.id = friendssym.user_id
    JOIN USERS as u2 ON friendssym.friend_id = u2.id      
GROUP BY u1.id
  HAVING COUNT(DISTINCT u2.userstatus) = 1

In traditional SQL, the earliest you can reference a column alias is the sentence ORDER BY. But MySQL and SQL Server allow access to HAVINGand GROUP BY.

+3
source

More articles:


All Articles