QUERY HELP!

At first glance, I notice the 2 "Not In" clauses. It seems to me you
could join the two tables as though you are getting all the data -
outer join certainly is OK. Assign tblepending an alias of a, and
tblependingworklog an alias of b. Then simply have a where clause as
you describe, where a.newmodelete = 'N' and a.migrationdate is not
null and b.pendingworkinfo not like 'mile 6%' The DMBS materializes
your query first, then applies any selection criteria in the where
clause. For the expert lurkers out there, that is over simplified,
but it is how I usually start formulating an SQL stmt. Tuning the
query to optimize (depending on how much or little the DBMS optimizes)
is another issue.

Respectfully, Steve Jackson

On 1 Nov 2002 10:21:58 -0700, "sheryl kemp" <dianedinero@aol.com>
wrote:

>
>
>I am using the syntax below to retrieve records for which the following scenario
>is true
>
>tblpending.newmoddelete= N
>tblpending.migrationdate is not null
>tblpendingworklog.pendingworkloginfo not like 'mile 6%'
>
>why isn't this query working
>
>SELECT DISTINCT dbo.tblPendingWorkLog.PendingID, dbo.tblPending.NEWMODDELETE
>FROM dbo.tblPendingWorkLog INNER JOIN
> dbo.tblPending ON dbo.tblPendingWorkLog.PendingID =
>dbo.tblPending.PendingID
>WHERE (dbo.tblPendingWorkLog.PendingID NOT IN
> (SELECT PENDINGID
> FROM TBLPENDING
> WHERE MIGRATIONDATE IS NULL)) AND (dbo.tblPendingWorkLog.PendingID
>NOT IN
> (SELECT DISTINCT PENDINGID
> FROM TBLPENDINGWORKLOG
> WHERE PENDINGWORKLOGINFO LIKE 'MILE 6%'))
>AND (dbo.tblPending.NEWMODDELETE = 'N')
>
>
>

Steve Jackson, Enterprise Section Leader