Joining One to Many Tables, Data Output Error

I need a solution ... Please

[scriptmasterdel]

Hello WebmasterWorld,

This is my first time posting in the database section i believe.

Here is my problem.

> I have a forum
> I want to join two tables initially to get the the "category_id" and the MOST RECENT "thread_id" FROM the tread table

---------------------------------------------------

--------------------
-- Category Structure
---------------------

CREATE TABLE `forum_categories` (
`category_id` int(11) NOT NULL auto_increment,
`category_name` varchar(255) NOT NULL default '',
`category_shortname` varchar(255) NOT NULL default '',
`category_description` text NOT NULL,
`category_parent` int(11) NOT NULL default '0',
`category_position` int(11) NOT NULL default '0',
`category_online` int(11) NOT NULL default '1',
PRIMARY KEY (`category_id`)
) ENGINE=MyISAM;

--------------------
-- Thread Structure
--------------------

CREATE TABLE `forum_threads` (
`thread_id` int(11) NOT NULL auto_increment,
`thread_category_id` int(11) NOT NULL default '0',
`thread_poster` int(11) NOT NULL default '0',
`thread_title` varchar(255) NOT NULL default '',
`thread_message` text NOT NULL,
`thread_reason` varchar(255) NOT NULL default '',
`thread_date` varchar(50) NOT NULL default '',
`thread_online` int(11) NOT NULL default '0',
PRIMARY KEY (`thread_id`)
) ENGINE=MyISAM AUTO_INCREMENT=548 DEFAULT CHARSET=latin1

---------------------------------------------------

OK, so this is what i have tried

1. Selecting the categories table and joining the other tables on top...

SELECT
fc.category_id, fc.category_name,
count(ft.thread_category_id) as threadCount,
ft.thread_poster, ft.thread_title, ft.thread_message, ft.thread_date
FROM forum_categories fc
LEFT JOIN forum_threads ft ON fc.category_id = ft.thread_category_id
WHERE fc.category_online = 1
GROUP BY ft.thread_category_id DESC ORDER BY thread_date DESC;

>>> DIDN'T WORK
It selected the first entry for the thread and not the most recent.

2. Select all the threads and join the category table.

SELECT
thread_category_id,
fc.category_name,
count(ft.thread_category_id) as threadCount,
ft.thread_title, ft.thread_message, ft.thread_date
FROM forum_threads ft
LEFT JOIN forum_categories fc ON ft.thread_category_id = fc.category_id
GROUP BY ft.thread_category_id ORDER BY thread_id ASC;

>>> DIDN'T WORK AGAIN
It selected the first entry for the thread and not the most recent.

3. Then i tried using a MULTIPLE SELECT JOIN (on my local machine) and i did this. ...

SELECT fc.category_id, fc.category_name, count( ft.thread_category_id ) AS threadCount, thread_title, thread_message
FROM forum_categories fc
LEFT JOIN (
SELECT *
FROM forum_threads
ORDER BY thread_id DESC) ft ON ft.thread_category_id = fc.category_id
GROUP BY ft.thread_category_id

>>> IT WORKED, EXACTLY AS I WANTED IT TO

However my hosting company doesn't have the newest version of mysql installed so i can not preform multiple select statements.

I need a solution.

Please Help

All questions welcome..

Del

[coopster]

Subqueries became available in MySQL 4.1. If all you want is

>>to get the "category_id" and the MOST RECENT "thread_id"

as you mentioned, why not simply JOIN the two and grab the one with the latest date?

[scriptmasterdel]

I am running "MySQL 4.0.15", i have no control over this unless i change my host but at the moment this is not an option because i just paid for a years hosting.

Can you supply an example of this please.

As you can see i have joined the tables but i am getting the first result even if i specify the ordering in the order clause.

Thanks for your time.

Del