Query Assistance

I have the following query that yields the most recent status for a
record based on both the last date the record was modified and the
status of the record.

I am having trouble with the LEFT OUTER JOIN dbo.SAF2_MtgDaysTimes AS
R ON R.CESAFID portion. It is yielding multiple records from the
MtgDaysTimes table and, in turn, creating duplicates.

How can I modify this query to pull only the most recent MtgDaysTimes
record (based on Max recordID)? I have tried several approaches all
of which produce errors. Could this be done by adding an additional
subquery?

Please advise.

Thanks.


SELECT C.CESAFID, C.Term, C.Subj, C.SubjOther, C.Crse, C.CRN,
C.Course_Title, C.Inst1, C.Inst2, C.NewInstructor, C.CH, C.Dept,
C.Mode, C.Cap, C.Site, C.SiteOther, S.ActionDate, S.Status, S.CESAFID,
S.DepartmentSignature, R.MtgDaysTimeID , F.EID, F.FirstName,
F.LastName FROM dbo.SAFs AS C INNER JOIN dbo.SAF2_Status AS S ON
S.CESAFID = C.CESAFID LEFT OUTER JOIN dbo.SAF2_MtgDaysTimes AS R ON
R.CESAFID=C.CESAFID LEFT OUTER JOIN dbo.Faculty AS F ON F.EID =
C.Inst1 WHERE C.Term=Tvar AND C.Dept='Dvar' AND S.Status IN
('DEPTAPPROVED', 'DEPTMODDETAILS', 'DEPTMODSCHD', 'CEBANNERHOLD',
'CEBANNERSETUP', 'CEPAYINPROGRESS', 'CEPAYRELEASE', 'CESITEREVIEWED',
'DEPTMODCOMP', 'CEMODDETAILS', 'DEPTPPRFRM') AND S.ActionDate =
(SELECT MAX (S1.ActionDate)
FROM dbo.SAF2_Status AS S1
WHERE S1.CESAFID=C.CESAFID AND S1.Status NOT IN ('DEPTRMVLST',
'SAFNOTE'))
ORDER BY C.Subj, C.Crse
.

Relevant Pages

  • Left Outer Join: Index Seek not providing all index columns
    ... We have a left outer join query which is intended to prove ... The wrinkle is that the SQL Server 2000 query optimizer is generally ... left outer join entextractitems eei ...
    (microsoft.public.sqlserver.server)
  • Re: Cond. Formatting w/ DLookup
    ... I'd like to be able to indicate on the first form, ... form's record source to be a query with an outer join of the ... This form is continuous (actually a subform), ... Using your excellent idea of the outer join, ...
    (microsoft.public.access.formscoding)
  • Re: Optimising the Query
    ... SUM ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
    (comp.databases.oracle.misc)
  • Re: Cond. Formatting w/ DLookup
    ... I'd like to be able to indicate on the first form, ... IvId field, then a better way would be to change the first ... form's record source to be a query with an outer join of the ... Using your excellent idea of the outer join, ...
    (microsoft.public.access.formscoding)
  • Re: CONTAINS performance
    ... How can I predict the scalability of the simple query given that I don't ... but will measure the FTS with multiple clients issuing random FTS queries. ... B on A.bid=B.id left outer join ... the SQL Server query optimizer executes this query in the optimal manner ...
    (microsoft.public.sqlserver.fulltext)