-3

I'm storing threads for a forum with a parent / child relationship as follows:

CREATE TABLE forum_threads (
  thread_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
  topic_id INTEGER UNSIGNED NOT NULL,
  user_id INTEGER UNSIGNED NOT NULL,
  title VARCHAR(100) NOT NULL,
  body TEXT NOT NULL,
  create_date DATETIME NOT NULL,

  PRIMARY KEY (thread_id),
  FOREIGN KEY (parent_id)
    REFERENCES forum_threads(thread_id),
  FOREIGN KEY (topic_id)
    REFERENCES forum_topics(topic_id),
  FOREIGN KEY (user_id)
    REFERENCES users(user_id)
);

New threads have parent_id = 0, whereas replies have parent_id = the thread_id being replied to.

I want to select the most recently updated (replied to) threads and display the results in a table as follows:

enter image description here

How can I do this?

SELECT * FROM forum_threads
WHERE topic_id = whatever AND parent_id = 0
WHAT NEXT???

I'm not sure if this can be done with pure SQL, or if I should manipulate the results with PHP, or if I should try another approach all together?

mister martin
  • 6,197
  • 4
  • 30
  • 63
  • This link would help you for the same: http://stackoverflow.com/questions/15049133/mysql-select-only-newest-message-from-distinct-threads-order-by-timestamp-priva – Arun Jain Jul 04 '13 at 07:09

2 Answers2

0

Use the below query:

SELECT * FROM forum_threads
     WHERE topic_id = whatever AND parent_id = 0
     ORDER BY updated_date DESC limit 1;

this will give you the most update record.

Code Lღver
  • 15,573
  • 16
  • 56
  • 75
  • @mistermartin from where you are showing it in the result.it will be in your db. it is just exemption of mine. if you have created date then you can sort by that field. this is a table field name. – Code Lღver Jul 04 '13 at 13:24
  • `updated_date` is not in my table. I don't want to simply order by `create_date`. Please reread the question. – mister martin Jul 04 '13 at 13:45
0

You can simply do this in SQl

SELECT ft.* FROM  forum_threads AS ft
JOIN forum_threads AS ft1 ON ft.id = ft1.thread_id
WHERE topic_id = whatever AND parent_id = 0
ORDER BY ft.create_date DESC
LIMIT 1
Ramchandra Reddy
  • 1
  • 1