MySQL get all rows where id = xxx and where the newest column in other table in row with same id is greater than one
I have two mysql-tables: tbl_post
and tbl_post_public
tbl_post
looks like this:
id | post_id | author | date (unix time)
--------------------------------
0 | xxxxx1 | User1 | 1489926071
1 | xxxxx2 | User2 | 1489926075
2 | xxxxx3 | User3 | 1489926079
this table includes all posts by users
now I have the tbl_post_public
table: this table contains the information, if the post of the user should be public. if the user changes from public to private, the row doesn't get updated. it just adds a new row with the same post_id but with a newer timestamp 0 = public | 1 = private
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
^
|
so, in the result I want to have 10 Rows (LIMIT 10)
ORDERED BY the date-column in tbl_post DESC WHERE author="User1" and WHERE the newest(date-column in tbl_post_public) row in tbl_post_public (which has the same post_id) and has public = 0