Posted by Simon Hayes on 09/17/05 14:40

olanorm@gmail.com wrote:
> 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?
>

You can only reference a column alias in the ORDER BY clause, not in the
WHERE clause. You could repeat the whole subquery in the WHERE clause,
but using an outer join is probably easier:

select
k.UserID,
k.LastName,
k.FirstName,
kscr.Answer as 'myColumnAlias'
from
dbo.Users k
left outer join dbo.Results kscr
on k.UserID = kscr.UserID
where
kscr.Answer is not null

Simon

[Back to original message]