Filter Scalar Subquery

[daonho]

I've spent most of the day trying to research but still not able to find the answer. When trying to run the code below, I get an error message saying that "new_user_n ame" doesn't exist. The code below run correctly without the last filter (WHERE new_user_name = 'a'). Similar code work just fine using MySQL but I can't understand why SQL Server return an error message. Any thought on this?

SELECT
  id,
  first_name,
  (SELECT user_name FROM table2 WHERE table2.user_id = users.id) as new_user_name
FROM users
WHERE new_user_name = 'a'

[gpl]

new_user_name cannot be referenced in the outer query as it is an alias for the subquery

why not just use a join?

[daonho]

The actual subquery is function, which uses the "coalesce" function to return a comma separated list of organizations belong to that user. Therefore, it is not really possible to perform an inner join.

[gpl]

ok - I dont have SQL server available to me, but you would have to do something like this:

SELECT 
  id, 
  first_name, 
  (SELECT user_name FROM table2 WHERE table2.user_id = users.id) as new_user_name 
FROM users 
WHERE (SELECT user_name FROM table2 WHERE table2.user_id = users.id) = 'a'