Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Question Unanswered: Which one is better: selecting in one query or more ?

    Hi everybody,
    I have a problem with slecting with 2 table 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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is faster and way more reliable:
    Code:
    SELECT t.subject
         , p.id_user
      FROM forum_topics AS t
    INNER
      JOIN forum_posts AS p
        ON p.id_topic = t.id_topic
       AND p.post_date = 
           ( SELECT MAX(post_date)
               FROM forum_posts
              WHERE id_topic = t.id_topic )
    ORDER
        BY p.post_date DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    Posts
    3
    Thank you sooo much r937,
    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??

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "i need to group by id_topic" mean?

    the query i gave you will return exactly one row per topic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2008
    Posts
    3
    Practically yes it is , but when I try not!

    Because I noticed that I have posts that has exactly the same timestamp on my table which I use to test.

    Now i changed and it works.

    I thank you r937.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •