[ACCEPTED]-Show only most recent date from joined MySQL table-join

Accepted answer

Score: 31

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 3 known as a "groupwise maximum" query

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

Score: 4

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 2 subquery.

Alternative for when there are 1 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

Score: 0

Could you not just do a simple join, and 2 order by date added, and grab only the first 1 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

Source: stackoverflow.com

How to join two tables permanently with SQL?

MYSQL - Select only if row in LEFT JOIN is not present

Mysql query to join three tables

MySQL query with two joins

MySQL - Join Based on Date

When to use a left outer join?

MYSQL Left Join how do I select NULL values?

How to update from select with a Join

MySQL Join Multiple Rows as Columns

MySQL UPDATE with SELECT SUM from different table

Cookie Warning

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.