Hi all, I have an SQL issue that has kept me busy for over 2 hours straight now and I still haven't been able to solve it. I'm probably not thinking clear anymore by this time, but I also don't have any clues left, so I hope any of you can be of help.

One of my clients has a forum running. On the frontpage of his site, he wants a table cell that displays the forum topics with the most recent activity, but only from forums that are available to the public and not from private forums. For this to happen, I need information from 2 tables:

ibf_topics (Topic table)
- tid (topic id, used to create a link to the forum topic)
- title (title of the topic, also used in the link on the frontpage)
- forum_id (the id of the forum this topic is in)

ibf_posts (Posts table)
- pid (selected descending, to determine the most recent post)
- topic_id (the id of the topic this post belongs to)

Now, because this has to be checked within an existing CMS that I did not write, I do not have full freedom to just "mess around" I can only use the query function that came with the CMS, because I do not have the direct login information to the database. This means that I have to everything in 1 query. This pretty much what I want to be happening, but it all has to happen in 1 single query:

SELECT DISTINCT topic_id FROM ibf_posts ORDER BY pid DESC

and then

SELECT tid, title FROM ibf_topics WHERE tid=topic_id AND forum_id IN('1', '2', '18', '53');
(Note that topic_id should be fetched from the query above, since that is in the ibf_posts table).

I want this process to repeat itself 4 times, so the last 4 active topics show up.

So summarized, this is what need to be done:

  • Fetch post id's in descending order, so latest posts come first
  • Check if post is posted in any of the public forums
  • Check if this topic is not already in the "last 4 threads" list
  • If all ok, select the title and topic_id and return these values

I hope this makes sense, it's kind of complicated to explain.

I tried messing around with LEFT JOIN and INNER JOIN, since that looks pretty much like what I need, but I never used those keywords before so I'm messing everyting up.

Hope someone can help.

    IPB (at least in 2.0+, though I'm sure it's in 1.3) has something along the lines of last_post in the topics table, so you don't need any joins - just do something like this (fields may not be correct names):

    SELECT tid, title FROM ibf_topics WHERE forum_id IN ( 1, 2, 3 ) ORDER BY last_post DESC

      try this - if i'm understanding u correctly it should work!:

      SELECT DISTINCT a.topic_id, b.tid, b.title, t.forum_id FROM ibf_posts a, ibf_topics b WHERE b.tid = a.topic_id AND forum_id IN (1, 2, 3)

        7 days later
        B'man wrote:

        IPB (at least in 2.0+, though I'm sure it's in 1.3) has something along the lines of last_post in the topics table, so you don't need any joins - just do something like this (fields may not be correct names):

        SELECT tid, title FROM ibf_topics WHERE forum_id IN ( 1, 2, 3 ) ORDER BY last_post DESC

        Ah thanks !! How did I miss that? Well, it works perfectly 😃

          Write a Reply...