Limiting Results of Sub-Query

Databases
1

I have three tables in my database:

tbl_documents
DocumentID
DocumentName
DocumentFilename
AuthorID

tbl_authors
AuthorID
AuthorName
AuthorEmail

tbl_updates
UpdateID
DocumentID
UpdateDate (The date it was last updated)
NextUpdate (The date it needs to be updated next)
AuthorID (This is only used to record the person who made the update)

Each month, I send an automatically generated email is sent to Authors letting them know which of their documents are due for an update in the next 30 days.

Here is my current query:

SELECT doc.DocID, doc.DocName, doc.DocFilename, updt.NextUpdate, athr.AuthorID, athr.AuthorName, athr.AuthorEmail
FROM tbl_documents AS doc
INNER JOIN tbl_updates AS updt
USING (DocID)
INNER JOIN tbl_authors AS athr
ON doc.AuthorID = athr.AuthorID
WHERE doc.DocID IN
[INDENT](SELECT updt.DocID
FROM tbl_updates AS updt
WHERE updt.NextUpdate < (30 days from now)
AND updt.NextUpdate > (today)
ORDER BY updt.NextUpdate DESC)[/INDENT]
ORDER BY athr.AuthorID ASC

My dates are tracked with UNIX timestamps so where it says (30 days from now) and (today), those are the appropriate timestamps.

This query works fine if the document has only one record in tbl_updates with a NextUpdate date in the next 30 days. However, some documents are updated more frequently and will show up for each record from tbl_update with a NextUpdate date in the next 30 days.

For instance, “Policy A” is set to update monthly. Each time the document is updated, the value in the NextUpdate field will default to a month from whatever day it is being updated (Updated on Jun 15, NextUpdate = July 15). It was updated 4 times in April (4/5, 4/10, 4/15, 4/20) so there are four records on tbl_updates where the NextUpdate date is within the month of May (5/5, 5/10, 5/15, 5/20). I only want it to show the most recent record from tbl_update (the one from the 20th).

I do reasonably well with SQL but I am not a guru. I hope someone out there can shed some light on what I am missing.

Thanks,

-B

2

Okay, so it took me a while and I got sidetracked on a couple of other projects but I came up with a solution…

…only to realize that I discovered the solution that was posted originally.

Thanks for the help! It worked perfectly despite my own stupidity.

3 
SELECT doc.DocID
     , doc.DocName
     , doc.DocFilename
     , updt.NextUpdate
     , athr.AuthorID
     , athr.AuthorName
     , athr.AuthorEmail
  FROM tbl_documents AS doc
INNER 
  JOIN ( SELECT DocID
              , MAX(NextUpdate) AS most_recent
           FROM tbl_updates 
          WHERE NextUpdate < (30 days FROM now)
            AND NextUpdate > (today)
       ) AS m
    ON m.DocID = doc.DocID
INNER
  JOIN tbl_updates AS updt
    ON updt.DocID = doc.DocID
   AND updt.NextUpdate = m.most_recent
INNER 
  JOIN tbl_authors AS athr
    ON athr.AuthorID = doc.AuthorID
ORDER 
    BY athr.AuthorID ASC

:slight_smile:

4

Thanks for the reply!

That worked but after I input it, I realized that I didn’t communicate my desired results quite right.

When I said that I was looking for one result, I was actually looking for one result per document for each SME. I guess I didn’t carry it out far enough.

Take Steve Smith who is the author of “Policy A”, “Policy B” and “Policy C”: all three documents have an “NextUpdate” in the month of June. “Policy A” was revised three times in May so it actually has three records from tbl_update with “NextUpdate” dates in June.

My original query would give me these results:
Steve Smith - Policy A - June 15
Steve Smith - Policy A - June 10
Steve Smith - Policy A - June 5
Steve Smith - Policy B - June 7
Steve Smith - Policy C - June 20

I would actually like it to return:
Steve Smith - Policy A - June 15
Steve Smith - Policy B - June 7
Steve Smith - Policy C - June 20

I hope that is doable.

Thanks again for your help. I appreciate it.

5

nope, ya lost me

6

Can you show the actual query you used then? Because r937’s query would show the most recent version of each document.

It wouldn’t show:
Steve Smith - Policy A - June 15
Steve Smith - Policy A - June 10
Steve Smith - Policy A - June 5

but only:
Steve Smith - Policy A - June 15