>>>>Hi,
>>>>Here's a very simplified version of a view (named History):

SELECT Id, Date,
>>>>FROM  dbo.SCHeaders
>>>>UNION
>>>>Select Id, Date
>>>>FROM dbo.Allocations
>>>>UNION
>>>>SELECT Id, Date
>>>>FROM dbo.Detail

The view will always be used with a specific ID e.g :
>>>>SELECT * FROM History WHERE ID='Fred'
>>>>In this scenario there will always be only one record retrieved from the SCHeaders table.
>>>>I need to exclude any records with a date that is earlier than that of the SCHeaders record from the result.
>>>>FWIW this only need apply to the Detail table - there will be no such records in the Allocations table.
>>>>
>>>>A Sproc could be used if it is preferable....
>>>>TIA
>>>
>>>Do an inner join and include the date condition in the Where clause?
>>
>>Hi,
>>After I bit of head scratching I gave up on this and modified the Linq that queried the view so that it dropped the surplus rows - so I no longer need to achieve this directly in SQL. That said, I'm still curious as to what would have been the best SQL solution. Can you expand a bit on what you are suggesting ?
>>Best,
>>Viv
>

>

>
>Viv,
>
>I don't have access to an sql server - so forgive me the foxpro syntax - you won't have any problem translating
>
>

>select 	Id, ;
>			Date ;
>		from SCHeaders ;
>		where	( Id = ?pId) ;
>	union all ;
>	select 	Id, ;
>			Date ;
>		from Allocations ;
>		where	( Id = ?pId) ;
>	union all ;
>	select 	Id, ;
>			Date ;
>		from Detail ;
>		where	( Id = ?pId) ;
>			and	( date >= ( select Date from SCHeaders where 	( Id = ?pId) ) )
>

Thanks! I sort of thought it would need to be a SP.
Don't know what it is with me and SQL. Half the time I can't even write valid syntax - let alone come up with something that might give the right result :-{