Page 1 of 1

Help with table join?

Posted: Mon Apr 25, 2005 11:34 am
by eos
Hi,

I'm trying to output forum topics in order of latest posted using my date column (timestamp) from my forum_posts table.

Here is what I'm got so far but it doesn’t seem to be working:

Code: Select all

SELECT DISTINCT 
  `forum_topics`.`forum_id`,
  `forum_topics`.`topic`,
  `forum_topics`.`date`,
  `forum_topics`.`views`,
  `forum_topics`.`id`
FROM
  `forum_topics`
  JOIN `forum_posts` ON (`forum_topics`.`id` = `forum_posts`.`topic_id`)
ORDER BY
  `forum_posts`.`date` DESC

Posted: Mon Apr 25, 2005 11:40 am
by phpScott
'date' is a bad choice for a column as it is a data type in mysql you might want to change it too something like date_posted.

That might actually solve your problem too.

Posted: Mon Apr 25, 2005 11:56 am
by eos
Thanks,

I've changed the date colums name as you suggested:

Code: Select all

SELECT DISTINCT
ft.id,
ft.topic,
ft.date_posted,
ft.views,
ft.forum_id
FROM
forum_topics as ft
JOIN forum_posts as fp
ON ft.id = fp.topic_id
GROUP BY ft.id 
ORDER BY fp.date_posted DESC
I'm still getting the same results though.

Posted: Thu Apr 28, 2005 5:28 pm
by feyd
post the table structure in SQL format please.