>>>>>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) ;
Gregory