You are here: Re: Using IS NOT NULL on column result from subquery « MsSQL Server « IT news, forums, messages
Re: Using IS NOT NULL on column result from subquery

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация