help with alias in WHERE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with alias in WHERE

Author

Topic

hillng
Starting Member

5 Posts

Posted - 2004-05-20 : 04:58:06

Anyone can help with this problem
Query like this

Select distinct p.system_id, p.id As [pid],
pn=(select page_no from customer_product_identity cp where p.id=cp.product_id)
from product p
Where pn Like '%199%'

will give error of "Invalid column name", use Having also same.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-20 : 05:23:06

[code]
Select distinct p.system_id, p.id As [pid],
pn=(select page_no from customer_product_identity cp where p.id=cp.product_id and page_no like '%199%')
from product p
[/code]

hillng
Starting Member

5 Posts

Posted - 2004-05-20 : 05:33:46

Thanks for quick reply!

But maybe I didn't write my problem clearly.
The "page_no" is search option, once page_no like 199, return result.
If use the query above, whatever fulfill the search option, there will be results return, difference is the field "pn" will return NULL if page_no Not Like 199. What I need is if page_no Not Like 199, no result will return.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-20 : 05:45:23

[code]
Select distinct p.system_id, p.id As [pid], cp.page_no
from product p
inner join customer_product_identity cp on p.id=cp.product_id
where cp.page_no like '%199%'
[/code]

If page_no doesn't exist, you won't get results..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources