date is an alias created in same level so cant be used in where like that. so you need to tweak it like this
select *
from
(
select (select top 1 date from pass_in_trip p where p.trip_no = t.trip_no) as [date]
from trip t
)p
where [date] is not null
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Where by one column
Posted - 01/30/2013 : 04:19:48
