Mitchell Peek wrote:

>
>
> Now, in this query, I want all "deal"s and the last entry of
> "Discussion" where the deal has been edited since 12/1, or, a
> "discussion" or "attachment" has been added since the same date.
>
> Select D.Somecolumn, C.SomeColumn
> from Deal D
> left join Discussion C on C.Deal_Id=D. Id and C.Id=(Select Max(c2.ID)
> from discussion c2 where c2.deal_id=d. id)
> Where D.editDate >= '12/1/2006'
> OR (Exists ( select 1 from discussion DS where DS.Deal_ID=D. ID AND
> DS.CreateDate >= '12/1/2006' ))
> or (Exists ( Select 1 from Attachment A where A.Deal_ID=D. ID AND
> A.CreateDate >= '12/1/2006') )
> Order by 2
>
> however, this query does not use an index, but rather natural for D...
>

The first case can use an index, because there is a simple selection
issue: D.editdate either is or is not within the range specified.

When you add the EXISTS in a correlated sub query, as a look up, it has
to perform that look up on every row in D, hence the natural.

There also appears to be an inconsistency in your approach. The outer
query is Deal ON Discussion WHERE D.editdate >= a value. Then you add a
subquery involving the same two tables, to test whether DS.Createdate
might also be within a certain range. I would rewrite this part as:

Select D.Somecolumn, C.SomeColumn
from Deal D
left join Discussion C on C.Deal_Id=D. Id and C.Id=(Select Max(c2.ID)
from discussion c2 where c2.deal_id=d. id)
Where ( (D.editDate >= '12/1/2006')
OR (DS.CreateDate >= '12/1/2006' ) )

There are two obvious ways of achieving the third part.

1. Add a second left join (Attachment on Deal) and a third condition in
the where clause (A.CreateDate >= value)

2. Use a union to add the results of a query joining Deal on Attachment
with a single test in the where clause: (A.CreateDate >= value).

regards
ian