Get second tables latest value only

[TitanKing]

Hello everyone, I have a situation, its probably simple yet for me its not...

Here goes...

I have a Table, lets call it user,

Table 1
(user)
user_id | Name
1 | Piet Pompies
2 | Koos Viljoen

then I have a second table called posts,
Table 2
(posts)
post_id | Posts |user_id
1 | This was my first post | 1
2 | This was my last post | 1
3 | Another Posting Made | 2
4 | Another one | 2
5 | And the last one | 2

Now I would like to return results that shows only the last post made like this

Now I want to display the results like this through a while loop...

Result of user_id 1: Piet Pompies | This was my last post
Result of user_id 2: Koos Viljoen | And the last one

Please help me with this, any help would be highly appreciated ! :o

[Lars_Berg]

select name,posts
from table1 t1 inner join table2 t2 on t1.user_id = t2.user_id
where t2.post_id in (
select max(post_id)
  from table2
where table2.user_id = t2.user_id)

[TitanKing]

Thank you for you reply, you do not know how much this means to me, I will get back to you with a reply of how it worked...

[TitanKing]

What is the t1 and t2 for, is this the right syntex as I am getting an error, the code looks right, I am just wondering bout the syntex, damn 🙁

Help me please...

[Lars_Berg]

t1 and t2 are arbitrary names. T1 is only used for simplification, insetad of table1 t1 is used. T2 is needed to distinguish between the two instances of table2.

What does your query look like?

What error do you get?

[TitanKing]

I appreciate it ver much Lars Berg, you are my HERO !

select full_name, post

from ams_client_id t1

inner join ams_posts t2

on t1.client_id = t2.client_id

where t2.post_id in

(select max(post_id)

from ams_posts

where ams_posts.client_id = t2.client_id)

[TitanKing]

Ok I dont get an error anymore with above code, let me add the rest and see if I get the error again, Have I mentioned to say THANK YOU !!!

[TitanKing]

I am so close I smell it, here is the sql that gives me an error 🙁

select ams_client_id.client_id as file_id, ams_client_id.file_number, ams_client_id.full_name as name_surname,
ams_client_id.time_posted as time_started, ams_client_id.area, ams_client_id.consultant,

ams_posts.time_posted, ams_posts.poster, ams_posts.post_id

from ams_client_id t1

inner join ams_posts t2

on t1.client_id = t2.client_id

where t2.post_id in

(select max(post_id)

from ams_posts

where ams_posts.client_id = t2.client_id)

[TitanKing]

WOOOOOO H !!! !! WHAAAAAAA !!!!!!!! GOT IT SORTED !!!!!!!!!!!!!!!!! HAHAAHAHAH !!!!!!!

select t1.client_id as file_id, t1.file_number, t1.full_name as name_surname,
t1.time_started, t1.area, t1.consultant, t2.time_posted, t2.poster

from ams_client_id t1

inner join ams_posts t2

on t1.client_id = t2.client_id

where t2.post_id in

(select max(post_id)

from ams_posts

where ams_posts.client_id = t2.client_id)