[newbie] ordering results from one table by related info in another

X-Enigmail-Version: 0.89.0.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 49
Message-ID: <xeand.210$9n3.117@newsfe2-gui.ntli.net>
Date: Thu, 18 Nov 2004 23:28:29 GMT
NNTP-Posting-Host: 213.106.90.21
X-Complaints-To: http://www.ntlworld.com/netreport
X-Trace: newsfe2-gui.ntli.net 1100820509 213.106.90.21 (Thu, 18 Nov 2004 23:
28:29 GMT)
NNTP-Posting-Date: Thu, 18 Nov 2004 23:28:29 GMT
Organization: ntl Cablemodem News Service
Xref: number1.nntp.dca.giganews.com alt.php.sql:21819

I'm using php/access ('cos I'm not allowed to put MySQL on the server!)

I'm making a forum, and I have a table for the thread info, and another
for the posts.

Thread:

+--------------------+
| id | name | locked |
|----|------|--------|
| 1  | one  |  false |
| 2  | two  |  false |
| 3  | tree |  false |
+----+------+--------+

Posts:
+----+--------+------------+--------+------------------+---------+
| id | thread |    post    | poster |       date       | visible |
+----+--------+------------+--------+------------------+---------+
| 1  |    2   | post text  |   1    | 1/1/2004 1:00 AM |   true  |
| 2  |    1   | post text  |   5    | 1/1/2004 1:00 AM |   true  |
| 3  |    2   | post text  |   5    | 1/1/2004 1:10 AM |   true  |
| 4  |    1   | post text  |   5    | 1/1/2004 1:00 AM |   true  |
| 5  |    2   | post text  |   5    | 1/1/2004 1:20 AM |   true  |
| 6  |    1   | post text  |   5    | 1/1/2004 1:00 AM |   true  |
| 7  |    3   | post text  |   5    | 1/1/2004 1:30 AM |   true  |
| 8  |    1   | post text  |   5    | 1/1/2004 1:00 AM |   true  |
+----+--------+------------+--------+------------------+---------+

Trying to find a query that'll give me all the fields in the Thread
table, ordered by the latest post for the thread. That's so I can
iterate thru the results of the threads, and display:

thread.name, posts.poster, posts.date_of_last_post

with the thread most recently posted at the top, down to the oldest posted.

I've been on this for two days now, trawling thru various boards and
newsgroups, looking for someone already asking the same. Surely this is
a pretty common one! Maybe it's so simple nobody else needs to ask.

Normally I'd just add "last posted" to the threads table, updated by the
script that handles the posts and be done with it, but I really want to
get my head around this normalisation thing! Anyone got any ideas for
the SQL that'll give me what I need?

Thanks

Jim

Jim Evans <longhairedjim@ntlworld.com> wrote in message
news:<xeand.210$9n3.117@newsfe2-gui.ntli.net>...
>
> I'm making a forum, and I have a table for the thread info,
> and another for the posts.
>
> Thread:
>
> +--------------------+
> | id | name | locked |
> |----|------|--------|
> | 1  | one  |  false |
> | 2  | two  |  false |
> | 3  | tree |  false |
> +----+------+--------+
>
> Posts:
> +----+--------+------------+--------+------------------+---------+
> | id | thread |    post    | poster |       date       | visible |
> +----+--------+------------+--------+------------------+---------+
> | 1  |    2   | post text  |   1    | 1/1/2004 1:00 AM |   true  |
> | 2  |    1   | post text  |   5    | 1/1/2004 1:00 AM |   true  |
> | 3  |    2   | post text  |   5    | 1/1/2004 1:10 AM |   true  |
> | 4  |    1   | post text  |   5    | 1/1/2004 1:00 AM |   true  |
> | 5  |    2   | post text  |   5    | 1/1/2004 1:20 AM |   true  |
> | 6  |    1   | post text  |   5    | 1/1/2004 1:00 AM |   true  |
> | 7  |    3   | post text  |   5    | 1/1/2004 1:30 AM |   true  |
> | 8  |    1   | post text  |   5    | 1/1/2004 1:00 AM |   true  |
> +----+--------+------------+--------+------------------+---------+
>
> Trying to find a query that'll give me all the fields in the Thread
> table, ordered by the latest post for the thread.

Try this:

SELECT Thread.id, Thread.name, MAX(Posts.date) AS MostRecent
FROM Thread LEFT JOIN Posts ON Thread.id = Posts.thread
GROUP BY Thread.id
ORDER BY MostRecent;

Cheers,
NC

Nikolai Chuvakhin wrote:
> Jim Evans <longhairedjim@ntlworld.com> wrote in message
>    news:<xeand.210$9n3.117@newsfe2-gui.ntli.net>...
> 
>
>
> Try this:
>
> SELECT Thread.id, Thread.name, MAX(Posts.date) AS MostRecent
>   FROM Thread LEFT JOIN Posts ON Thread.id = Posts.thread
>   GROUP BY Thread.id
>   ORDER BY MostRecent;
>
> Cheers,
> NC

Hi Nikolai

Thanks for the attempt. Didn't work, though. I get "You tried to execute
a query that does not include the specified expression 'name' as part of
an aggregate function" when I try it. I've tried it without the
thread.id, thread.name" but then it just prompts me for "MostRecent" so
I guess it's not assigning the value to it.

I think my brain is starting to leak from my ears......

Do it like this:

SELECT Thread.id, Thread.name, MAX(Posts.date) AS MostRecent
FROM Thread LEFT JOIN Posts ON Thread.id = Posts.thread
GROUP BY Thread.id, Thread.name
ORDER BY MostRecent;