All geek questions in one place

MYSQL JOIN two tables limit the results of the second table by date

I am trying to get a date from two tables using a MYSQL query. I want to join them together categories.cat_id=topics.topic_cat . Multiple entries can have the same topic_cat , so I want only SELECT from the last equal to MAX(topic_date) .

The following query shows the correct information from topics, and only one result for topic_cat and a result having the most recent date.

 SELECT topic_subject, topic_cat, topic_date FROM topics GROUP BY topic_cat DESC

Several lines may have the same value for topic_cat, but I only want to get and join only the last one, MAX (topic_date), and then join the query, which shows the following information from the category table.

 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;

This request displays the correct information except one. It shows topic_cat with the oldest entry or MIN(topic_date) . I tried the following to get topic_cat using the newest entry or MAX(topic_date) , but without success.

 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

Any help or suggestions would be greatly appreciated.

So, here is an example of the data and the desired result associated with it.

Table 1 = Categories

 _______________________________________________________ | cat_id | cat_name | cat_description | ------------------------------------------------------- | 1 | james | Some information about james| ------------------------------------------------------- | 2 | myo | Some information about myo | ------------------------------------------------------- | 3 | brandon | Some information about brandon | -------------------------------------------------------

Table 2 = topics

 __________________________________________________ | 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 | ---------------------------------------------------------

I would like the query to return the following data based on the tables above:

 _________________________________________________________________________________________________ | 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 | ----------------------------------------------------------------------------------------------------------------

Hope this clarifies the situation.

+6
source share
2 answers

Try the following:

 ### SELECT * FROM categories c LEFT JOIN topics t ON c.cat_id = t.topic_cat WHERE c.cat_id IN (SELECT t1.cat_id FROM ( SELECT c.cat_id, c.cat_name, MAX(t.topic_date) AS maxdate FROM categories c LEFT JOIN topics t ON c.cat_id = t.topic_cat GROUP BY c.cat_name ) as t1 WHERE t1.maxdate = t.topic_date OR t.topic_date IS NULL ); ### without nulls SELECT * FROM categories c LEFT JOIN topics t ON c.cat_id = t.topic_cat WHERE c.cat_id IN (SELECT t1.cat_id FROM ( SELECT c.cat_id, c.cat_name, MAX(t.topic_date) AS maxdate FROM categories c LEFT JOIN topics t ON c.cat_id = t.topic_cat GROUP BY c.cat_name ) as t1 WHERE t1.maxdate = t.topic_date);
+4
source

Try to change

 LEFT JOIN (SELECT topic_subject, topic_cat, topic_date, topic_by FROM topics GROUP BY topic_cat DESC) AS topics

in

 LEFT JOIN (SELECT topic_subject, topic_cat, topic_date, topic_by FROM topics GROUP BY topic_cat ORDER BY topic_date DESC LIMIT 0,1) AS topics
+1
source

Source: https://habr.com/ru/post/954905/

More articles:


All Articles