There are the following tables:

Issues:

id |name ######## 1 Name1 2 Name2 3 Name3

Issue statuses

 id |name_id | date | status_id ############################### 1 1 ДАТА 2 2 1 ДАТА 3 3 2 ДАТА 2 4 5 ДАТА 1 5 3 ДАТА 4 6 2 ДАТА 4

Status

 id |name ######## 1 Name1 2 Name2 3 Name3

I need one request to select from the first table those records whose newest status is equal to the one needed.

I try to do

  SELECT t.*, (select `s`.`id` from `status` as `s` left join `issue_status` as `is` on `is`.`status_id` = `s`.`id` where `is`.`issue_id` = `t`.`id` order by `date` desc limit 1) as status_id FROM issue as t;

But then I need to do something like where on all the results, and on this field status_id that I received does not go out to make an additional selection. I do not know how to change the query to get the results I need.

  • You seem not to finish writing an example. you do not have from ... t . In general, you need to wrap your request in another select * from (ваш запрос) A where status_id=XXX . By the way, why do you need a status table in the subquery, I did not understand, there’s enough issue_status. I would even say that it is necessary. And then you and your left join will get some status even if it is not in the issue, and what kind of prediction is impossible - Mike
  • @Mike, Yes, I also think that I did something with the status table. And wrapped in another select everything works now. Thank! As it did not even think that one more it can be. - Moonvvell

0