Dear, a small stupor how to make a request. There are two tables, in one data for each object, and in the second its state (id, the time when there was a change). It is required to make a selection to get a list of all objects and their last change, that is, to display all objects and if the change in the selected date was then record this time, if there was no change (there is no corresponding table entry) then write NULL. How to make a request? Tried such a request but the condition is wrong a little. In different ways I tried or did not fully return or nothing.

SELECT `objects`.`id`, `objects`.`weight`, `objects`.`price`, `objects`.`status`, MAX(status.changeStatusTime) FROM `objects` LEFT JOIN `status` ON `status`.`id_obj` = `objects`.`id` WHERE DATE(status.changeStatusTime) = '2017-02-05' GROUP BY `objects`.`id`

<code> enter image description here </ code>

  • is status.id_obj the same foreign key referring to objects.id? Then why are they different in the example? To avoid misunderstandings, it would be nice to lay out the full structure somewhere on sqlfiddle.com. - Fat-Zer
  • I apologize, I messed up a bit with id_obj, there are 1 and 2 instead of 10 and 11 - Johnyboy
  • DATE(status.changeStatusTime) = '2017-02-05' condition DATE(status.changeStatusTime) = '2017-02-05' better written like this: status.changeStatusTime>= '2017-02-05' and status.changeStatusTime<'2017-02-05'+interval 1 day . It looks more cumbersome, but the search speed can be ten times higher, especially if there is an index on the field with a date. Applying any function to a table column causes the database to first perform calculations on each record, secondly it makes it impossible to use indexes and the query is forced to view the entire table - Mike

1 answer 1

If you guessed correctly with the meaning of the question, then something like this:

 SELECT o.id, o.weight, o.price, o.status, s.ts FROM `objects` AS o LEFT JOIN ( SELECT id_obj, MAX(changeStatusTime) AS ts FROM `status` WHERE DATE(status.changeStatusTime) = '2017-02-05' GROUP BY id_obj ) AS s ON s.id_obj = o.id
  • Thank you for what you need. Now I’ve figured out about the JOIN subquery - Johnyboy