There are offers and offers_problems . Communication one ( offer ) to many ( offers_problems ). In offers_problems there is an offer_id field and there is a create_datetime .

How do I receive the last entry when selecting offers (across the create_datetime field) of offers_problems ?

Need the latest from offer_problems by create_datetime .

  • ..left join ... order by create_datetime DESC - Victor Halauko
  • It is not clear what the “last record” means, when you select Join, all rows participate in your operation, and the type Join already gives the necessary ones - this is the first. Second, you can use the WHERE clause for the second Join to select the last entry from the offers_problems, or a nested SELECT, which is the same - Jony

3 answers 3

[1] Get the latest create_datetime values ​​for each offer_id :

 SELECT offer_id, max(create_datetime) as max_time FROM offers_problems GROUP BY offer_id

[2] Retrieving id's of offers from offers_problems with the latest create_datetime :

 SELECT op.id FROM offers_problems op JOIN ([1]) latest ON op.offer_id = latest.offer_id AND op.create_datetime = latest.max_time

[3] Result:

 SELECT * FROM offers JOIN offers_problems op ON offers.id = op.offer_id WHERE op.id IN ([2])

Final request:

 SELECT * FROM offers JOIN offers_problems op ON offers.id = op.offer_id WHERE op.id IN ( SELECT op.id FROM offers_problems op JOIN ( SELECT offer_id, max(create_datetime) as max_time FROM offers_problems GROUP BY offer_id ) latest ON op.offer_id = latest.offer_id AND op.create_datetime = latest.max_time )

    It is necessary to make a preliminary selection with sorting by date, and then from it already do a selection with grouping by offer.id. It turns out like this:

     SELECT * FROM ( SELECT o.*, p.`id` AS `problem_id`, p.`description` AS `problem_description` FROM `offer` o LEFT JOIN offer_problems` p ON p.`offer_id`=o.`id` ORDER BY p.`create_datetime` DESC ) tmp GROUP BY `id` ORDER BY `id` DESC
    • Does such a query work at all? You do GROUP BY id , and print SELECT * ! What do you think should return such a request? - Tema_Bel
    • Yes, this is a work request. A subquery is essentially a temporary table with fields from the offer and a couple of fields from offer_problems. The outermost SELECT * query is a selection of all the columns formed by the subquery. I checked this query in my database using the example of products and reviews to them - it gives the goods with the last review - Darevill
    • Interesting. Just read about it. This will work when ONLY_FULL_GROUP_BY is off. But this is not pure SQL ... - Tema_Bel
     SELECT * FROM offers INNER JOIN offers_problems ON offers.id=offers_problems.offer_id ORDER BY create_datetime DESC LIMIT 1