StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMYSQL JOIN two tables limit results from second table by date
text
Body
copied!<p>I am trying to retrieve date from two tables using a MYSQL query. I want to join them together were <code>categories.cat_id=topics.topic_cat</code>. Multiple entries may have the same <code>topic_cat</code>, so I only want to SELECT the most recent, which is equal to <code>MAX(topic_date)</code>. </p> <p>The following query shows the correct information from topics, with only one result per topic_cat and that result having the most recent date.</p> <pre><code>SELECT topic_subject, topic_cat, topic_date FROM topics GROUP BY topic_cat DESC </code></pre> <p>Multiple rows may have the same value for topic_cat, but I only want to retrieve and join only the most recent, MAX(topic_date) and then join to a query which shows the following information from the categories table.</p> <pre><code>SELECT categories.cat_id, categories.cat_name, categories.cat_description, topics.topic_subject, topics.topic_cat, topics.topic_date, topics.topic_by FROM categories LEFT JOIN topics ON categories.cat_id=topics.topic_cat GROUP BY cat_id; </code></pre> <p>This query displays the correct information, except one thing. It shows the topic_cat with the oldest entry, or <code>MIN(topic_date)</code>. I have tried the following to get the topic_cat by newest entry or <code>MAX(topic_date)</code>, but without success.</p> <pre><code>SELECT categories.cat_id, categories.cat_name, categories.cat_description FROM categories LEFT JOIN (SELECT topic_subject, topic_cat, topic_date, topic_by FROM topics GROUP BY topic_cat DESC) AS topics ON categories.cat_id=topics.topic_cat </code></pre> <p>Any help or suggestions would be greatly appreciated.</p> <p>Ok, so here is the sample data and associated desired result.</p> <p>Table 1 = categories</p> <pre><code>_______________________________________________________ | cat_id | cat_name | cat_description | ------------------------------------------------------- | 1 | james | Some information about james| ------------------------------------------------------- | 2 | myo | Some information about myo | ------------------------------------------------------- | 3 | brandon | Some information about brandon | ------------------------------------------------------- </code></pre> <p>Table 2 = topics</p> <pre><code>__________________________________________________ | topic_subject | topic_cat | topic_date | topic_by | ---------------------------------------------------------- | marcos | 2 | 2013-9-28 | User 1 | --------------------------------------------------------- | ferdinand | 2 | 2013-9-29 | User 2 | --------------------------------------------------------- | maria luisa | 2 | 2013-9-30 | User 1 | --------------------------------------------------------- | Isabella | 1 | 2013-8-24 | User 3 | -------------------------------------------------------- | Carlos | 3 | 2012-6-21 | User 2 | -------------------------------------------------------- | Enrique | 3 | 2011-4-2 | User 3 | --------------------------------------------------------- </code></pre> <p>I would like the query to return the following data based on the above tables:</p> <pre><code>_________________________________________________________________________________________________ | cat_id | cat_name | cat_description | topic_subject | topic_cat | topic_date | topic_by | ---------------------------------------------------------------------------------------------------------------- | 1 | james | Some information about james | Isabella | 1 | 2013-8-24 | User 3 | ---------------------------------------------------------------------------------------------------------------- | 2 | myo | Some information about myo | maria luisa | 2 | 2013-9-30 | User 1 | ---------------------------------------------------------------------------------------------------------------- | 3 | brandon | Some information about brandon | Carlos | 3 | 2012-6-21 | User 2 | ---------------------------------------------------------------------------------------------------------------- </code></pre> <p>I hope that clarifies things.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload