MySQL get all rows where id = xxx and where the newest column in other table in row with same id is greater than one

Ted James
2 min readApr 25, 2024

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

--

--

Ted James

The world is my office, and every destination is my inspiration