Show only most recent date from joined MySQL table

I have 2 tables, a “document” table and a “content” table. They look like this (simplified):

document table:
docID
docTitle

content table:
contentID
docID
dateAdded
content

For every content change, a new record is inserted into the “content” table. This way there is a complete history of all changes. I would like to get a list of all the documents, with the latest content joined. It should return the docID, docTitle, and the associated content record with the newest “dateAdded” value. My brain is failing me right now, how would I create this join?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This can be done with a subquery:

SELECT d.docID, docTitle, c.dateAdded, c.content
FROM document d LEFT JOIN content c ON c.docID = d.docID
WHERE dateAdded IS NULL
    OR dateAdded = (
        SELECT MAX(dateAdded)
        FROM content c2
        WHERE c2.docID = d.docID
    )

This is known as a “groupwise maximum” query

Edit: Made the query return all document rows, with NULLs if there is no related content.

Method 2

Use:

SELECT t.docid, 
       t.docTitle, 
       mc.dateAdded, 
       mc.content
  FROM DOCUMENT t
  JOIN (SELECT c.docid,
               c.content,
               MAX(c.dateAdded)
          FROM CONTENT c
      GROUP BY c.docid, c.content) mc ON mc.docid = t.docid 
                                     AND mc.dateadded = t.dateadded

This should be faster than a correlated subquery.

Alternative for when there are no content records for a document:

   SELECT t.docid, 
          t.docTitle, 
          mc.dateAdded, 
          mc.content
     FROM DOCUMENT t
LEFT JOIN (SELECT c.docid,
                  c.content,
                  MAX(c.dateAdded)
             FROM CONTENT c
         GROUP BY c.docid, c.content) mc ON mc.docid = t.docid 
                                     AND mc.dateadded = t.dateadded

Method 3

Could you not just do a simple join, and order by date added, and grab only the first record?

SELECT docTable.docId, docTable.docTitle from docTable
INNER JOIN content ON content.docID = docTable.contentID
WHERE docTable.docId = <some id>
ORDER BY content.dateAdded DESC

Method 4

This is a 2 query solution:

First query:

select docID, max(dateadded) from [TblContent] group by docID

Second query:

select [TblDocument].* from [TblDocument]  
inner join [TblDocument] on [TblDocument].[Docid]=[FirstQuery].[DocID]

Method 5

try this:

select d.id, d.docTitle, MAX(c.dateAdd)
from document_table d
left join content_table c
on d.id = c.docId
group by d.id

Here is the thinking behind it: suppose document table has record A related to content(1, 2, 3, 4) and B related to (5, 6, 7, 8)

document        content

A               1
            2
                    3
            4

B               5
            6
            7
            8

a inner join with max(dateadded) will give you

document        content     max(dateadded)

A               1           1-1-2009...

A               2           1-1-2009...

A               3           1-1-2009...

A               4           1-1-2009...

B               5           2-1-2009...

B               6           2-1-2009...

B               7           2-1-2009...

B               8           2-1-2009...

after group by document id you will get

document    content     max(dateadded)

A           1           1-1-2009...

B           5           2-1-2009...

note: content id does not necessary match the id of the max dateadded

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating