Given two tables, select all data from one table and only the most recent one from the other

advertisements

I'm trying to build a forum website that uses PHP and a MySQL database to store its category and topic content. On the homepage, I want to have a table that shows a list of all the categories and the most recent posted topic from each category.

I want to write a query that returns all of the categories from the category table and only the most recent topics for each category from the topics table.

My tables look like this:

categories

+--------+-------------+--------------------------+
| cat_id |  cat_name   |     cat_description      |
+--------+-------------+--------------------------+
|      1 | Automobiles | *Tim Taylor manly grunt* |
|      2 | English     | How to English           |
|      3 | Gardening   | Lawn and Order           |
+--------+-------------+--------------------------+

topics

+----------+-----------------------+------------------------+-----------+----------+
| topic_id |      topic_name       |     topic_content      | topic_cat | topic_by |
+----------+-----------------------+------------------------+-----------+----------+
|        1 | '67 Chevy Question    | Weird knocking noise?  |         1 |        1 |
|        2 | You're vs Your        | What's the difference? |         2 |        3 |
|        3 | Jumper cables?        | The proper hookup      |         1 |        2 |
|        4 | '03 Pathfinder        | Next newest model?     |         1 |        1 |
|        5 | There, Their, They're | Know the difference    |         2 |        4 |
+----------+-----------------------+------------------------+-----------+----------+

I found a relevant answer on https://stackoverflow.com/a/12586263/7249891 under Trick #3, but after a couple of hours of fiddling, am unable to boil it down to a query that works for me.

My question is, how do I adjust my original query

SELECT c.cat_name AS Category, t.topic_cat AS Recent Topic
FROM categories c
JOIN topics t
WHERE c.cat_id = t.topic_cat

so it returns all the categories in the database, but only the most recent topic from each category in a result similar to this

+-------------+-----------------------+
|  Category   |     Recent Topic      |
+-------------+-----------------------+
| Automobiles | '03 Pathfinder        |
| English     | There, Their, They're |
| Gardening   | NULL                  |
+-------------+-----------------------+

Clarifications: In this forum, there are several categories created by admins that any user can post a topic in.

In a topic, the topic subject is a question asked by a user and the topic content is additional information about that question.

Cat_id and topic_id are both auto incrementing primary keys.

Topic_subject is a foreign key that references cat_id.

Assume that the most recent topic in the topics table is the one with the highest topic_id number because of the primary key behavior. There is also a date field in this table (which I realized last minute I forgot to include here).

There are two other tables I didn't list here: a users and replies table. Topic_by is a foreign key that references the users table.

If there are no topics in a category (the gardening category in my above example), we'll assume the PHP portion of the program will make that part of the list say "(none)".


First find the latest post in each category:

select topic_cat, max(topic_id) as latest_topic
from topics group by topic_cat

Then add that to your join conditions:

SELECT  c.cat_name AS Category, t.topic_name AS Recent_Topic
FROM categories c
left JOIN topics t on c.cat_id = t.topic_cat
left join (select topic_cat, max(topic_id) as latest_topic
        from topics group by topic_cat) as latest_topics
        on latest_topics.topic_cat = c.cat_id
        and latest_topics.latest_topic = t.topic_id
where latest_topics.topic_cat is not null or t.topic_cat is null;