MySql получает все строки, где id = xxx и где самый новый столбец в другой таблице в строке с одинаковым идентификатором больше одного

У меня две таблицы mysql: tbl_post и tbl_post_public

tbl_post выглядит следующим образом:

 id | post_id | author | date (unix time) -------------------------------- 0 | xxxxx1 | User1 | 1489926071 1 | xxxxx2 | User2 | 1489926075 2 | xxxxx3 | User3 | 1489926079 

эта таблица включает все сообщения пользователей

теперь у меня есть таблица tbl_post_public : эта таблица содержит информацию, если сообщение пользователя должно быть общедоступным. если пользователь переходит от общедоступного к частному, обновляется дозадатель строк. он просто добавляет новую строку с тем же post_id, но с более новой меткой времени 0 = public | 1 = частный

  id | post_id | public | date (unix time) ----------------------------------------- --> 0 | xxxxx1 | 0 | 1489926071 <--| this two rows have the same 1 | xxxxx2 | 1 | | post_id but the second is 2 | xxxxx3 | 0 | | public = 1. i need to get --> 3 | xxxxx1 | 1 | 1489926099 <--| the second row because its newer ^ | 

поэтому в результате я хочу иметь 10 строк (LIMIT 10) ЗАКАЗАННЫХ по столбцу даты в tbl_post DESC WHERE author = "User1", а WHERE – самая новая строка date-column в tbl_post_public в tbl_post_public (у которой есть тот же post_id ) и имеет public = 0

я надеюсь, вы понимаете мой вопрос и извините за мой плохой английский 🙂

Вы можете получить самую последнюю строку в общедоступной таблице различными способами. Если вы фильтруете сообщения, я бы рекомендовал коррелированный подзапрос:

 select p.* from (select p.*, (select pp.public from tbl_post_public pp where pp.post_id = p.post_id order by date desc limit 1 ) as latest_public from tbl_post p where . . . ) p where public = 0 order by date desc limit 10; 

Для достижения производительности вам понадобится индекс на tbl_post_public(post_id, date) .

Если у вас нет предложения where , и у вас есть индекс на tbl_post(date) , то это, вероятно, немного быстрее:

 select p.* from (select p.*, (select pp.public from tbl_post_public pp where pp.post_id = p.post_id order by date desc limit 1 ) as latest_public from tbl_post p order by p.date desc ) p where public = 0 limit 10;