> I'm having a dickins of a time with a particular query and am hoping
> someone here can help me.
>
> Using the following example;
>
> declare @SearchDate datetime
> set @SearchDate = '30 Nov 2005'
>
> declare @t1 table (t1id int, t1desc varchar(10))
> insert into @t1 (t1id, t1desc) values (1, 'Ed')
> insert into @t1 (t1id, t1desc) values (2, 'Bill')
> insert into @t1 (t1id, t1desc) values (3, 'Bob')
> insert into @t1 (t1id, t1desc) values (4, 'Fred')
> insert into @t1 (t1id, t1desc) values (5, 'John')
>
> declare @t1history table (t1id int, t1desc varchar(10), created
> datetime)
> insert into @t1history (t1id, t1desc, created) values (1, 'James', '01
> Jan 2005')
> insert into @t1history (t1id, t1desc, created) values (1, 'Frank', '05
> Jan 2005')
> insert into @t1history (t1id, t1desc, created) values (1, 'Henry', '10
> May 2005')
> insert into @t1history (t1id, t1desc, created) values (1, 'Joe', '28
> Nov 2005')
> insert into @t1history (t1id, t1desc, created) values (4, 'Toby', '21
> Oct 2005')
> insert into @t1history (t1id, t1desc, created) values (4, 'Brian', '25
> Oct 2005')
> insert into @t1history (t1id, t1desc, created) values (4, 'Horace', '28
> Nov 2005')
> insert into @t1history (t1id, t1desc, created) values (5, 'Ben', '21
> Oct 2005')
>
> declare @lookup table (val varchar(10))
> insert into @lookup (val) values ('Ben')
> insert into @lookup (val) values ('Frank')
> insert into @lookup (val) values ('Bill')
>
> --example query
> select *,
> (select top 1 t1desc from @t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @SearchDate +1 order by
> created desc) as t1deschistory
> from @t1 as Table1
>
>
> I want to filter the results returned from @t1 against those contained
> in @lookup
> I also need to be able to filter the results based on what the value
> for t1desc could have been in the past using @t1history and @SearchDate
>
> For example, with the date of '30 Nov 2005' I would expect the
> following;
>
> t1id          t1desc          t1deschistory
> ------------------------------------------------------------
>  2            'Bill'             null
>  5            'John'             'Ben'
>
> Changing the date to '15 Oct 2005' I would expect;
>
> t1id          t1desc          t1deschistory
> ------------------------------------------------------------
>  2            'Bill'              null
>
> and changing it again to '15 Jan 2005' I would expect;
>
> t1id          t1desc          t1deschistory
> ------------------------------------------------------------
>  1            'Ed'               'Frank'
>  2            'Bill'              null
>
>
> What I basically want to do is this;
> select *,
> (select top 1 t1desc from @t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @SearchDate +1 order by
> created desc) as t1deschistory
> from @t1 as Table1
> where t1desc in (select val from @lookup) or t1deschistory in (select
> val from @lookup)
>
> This gives the following error as expected;
>
> Server: Msg 207, Level 16, State 3, Line 28
> Invalid column name 't1deschistory'.
>
> Moving the sub-query into a join doesn't work either;
>
> select *
> from @t1 as Table1
> left join (select top 1 * from @t1history as t1history where
> t1history.t1id = Table1.t1Id and created <= @SearchDate +1 order by
> created desc) Table1History on Table1.t1id = Table1History.t1Id
> where Table1.t1desc in (select val from @lookup) or
> Table1History.t1desc in (select val from @lookup)
>
> This gives the following error;
>
> Server: Msg 107, Level 16, State 2, Line 28
> The column prefix 'Table1' does not match with a table name or alias
> name used in the query.
>
>
>
> Can anyone help?
>
> Many thanks in advance,
> Ed
>