Help with table join?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
eos
Forum Newbie
Posts: 2
Joined: Mon Apr 25, 2005 11:30 am

Help with table join?

Post 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
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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.
eos
Forum Newbie
Posts: 2
Joined: Mon Apr 25, 2005 11:30 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

post the table structure in SQL format please.
Post Reply