SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Limiting Results of Sub-Query

    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:
    Code SQL:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nope, ya lost me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •