Posted by olanorm on 09/17/05 14:10

I have a query where one or more of the columns returned is a result
from a subquery. These columns get their own alias. I want to filter
out the rows containing NULL from the subqueries but it just won't
work. When running columnAlias IS NOT NULL i just get the error
"Invalid column name 'columnAlias'.

This is the query:

SELECT k.UserId, k.Lastname, k.Firstname,
(SELECT kscr.answer FROM Results kscr WHERE kscr.UserID =
k.UserID) AS myColumnAlias
FROM Users k
WHERE myColumnAlias IS NOT NULL)



When running without the WHERE clause, I get the following results:

UserId Lastname Firstname myColumnAlias
113 Norman Ola jepps
820 Karlsen Kjell
830 Pens Jens juubidoo

What I want is to get rid of UserId=820. What am I doing wrong?

[Back to original message]