>>>>>>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 :-{
>
>

>

>What Mike probably meant was something like this
>
>

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

Which begs the question: which would be best? :-}