Hi everybody,
I have a problem with selecting using 2 tables here is the definition:

Table1: forum_topics (id_topic,subject ,topic_date)
Table2: forum_posts (id_post,id_topic,id_user,post,post_date)

as you can guess table 1 keeps the topics and table 2 contains the posts for topics.

Now I want to display last 10 topics on homepage with following data:
- name of topic,
- the id_user who posted last to the topic
and want to order them in a way that the topic which has the last post will be displayed at top.

is it posibble to make all with one query. Or is it a better way to handle it with php? Which one is faster? which one is reliable?

If it is posible to make it with one query how can I do this. I tried some querries but cant order it by last post

Thanks for your help.

It depends a lot of your indexes...
If your post table grows really big, the order by will become time consumming (I had to extract datas from a 3Go table once, and the order_by was crashing the server).

Otherwise, a simple left join would do the job nicely.

select 
  t.id_topic, t.subject, 
  p.id_user, p.id_post, p.post_date
from 
  forum_topics t, 
  forum_posts p
where t.topic_id=p.topic_id
order by p.post_date desc
limit 10 offset 0

Note that this query is ok for mysql/pgsql, but will need to be adapted for other db.

Thank you sooo much tripy,
query works fine but I need to group by id_topic because I want to display each topic once.
When I try grouping it, result don't come up with id_user who posted last to the topic

I know it is possible to get last poster with a new query in php while statement but this means making the same thing with 11 queries instead of one. I think this is not the best way.

Another way I can think is to push redundant data to forum_topics. (a new field like last_poster_id and updating it for each post). But I know this also is not a good approach.

Any ideas??

Here's how i'd do it:

 SELECT ft.subject, ft.topic_date, fp.id_post, fp.id_user, fp.post, fp.post_date     
   FROM forum_topics ft, forum_posts fp   
 WHERE ft.id_topic = fp.id_topic AND fp.id_post = 
                                            ( SELECT id_post 
                                              FROM forum_posts 
                                              WHERE id_topic = ft.id_topic 
                                              ORDER BY post_date DESC 
                                              LIMIT 0, 1 ) 
ORDER BY fp.post_date desc;

One more advice, use timestamp data type for post_date and topic_date fields.