[SOLVED] Question on getting/using info from 2 tables

[smerny]

I have a table "topics" with the columns:

 

ID, ID_board, name, description, views, order, locked

 

and then a table "posts" with the columns:

 

ID, ID_topic, ID_board, ID_poster, posttime, modifier, subject, body, modifiedtime

 

===================

 

What I want to do is create a query that

 

SELECTS

 

topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked

 

AND

 

posts.posttime (but ONLY on the post that has "posts.ID_topic = $id_topic" with the latest posts.posttime)

 

WHERE

 

topics.ID_board = $id_board, posts.ID_board

 

ORDER BY

 

posts.posttime DESC

 

=====================

 

Hope that is clear enough...

[Ken2k7]

You sort of almost answered your own issue.

 

SELECT t.ID, t.name, t.description, t.views, t.order, t.locked, p.posttime
FROM topics t
INNER JOIN posts p
     ON posts.ID_topic = $id_topic
WHERE t.ID_board = $id_board
     AND t.ID_board = p.ID_board
ORDER BY p.posttime DESC;

 

Is that right?

[smerny]

I'm not sure, because "$id_board" isn't really available... except within the query itself...

 

It's going to loop, and I want it to get all the topic information as well as information on the last post within that topic.

 

while($topics = mysql_fetch_array($result))

{

  [printing information like topic title/description as well as information on the time of the last post which i want it sorted by]

}

[kickstart]

Hi

 

Think something like this would do what you want.

 

SELECT topics.ID, topics.name, topics.description, topics.views, topics.order, topics.locked, postSub.LatestPostTime
FROM topics
LEFT OUTER JOIN (SELECT ID_board, MAX(posttime) AS LatestPostTime FROM posts GROUP BY ID_board) postSub
ON topics.ID_board = postSub.ID_board
ORDER BY postSub.LatestPostTime DESC

 

Basically join the topics table with a subselect of the posts table.

 

All the best

 

Keith

[smerny]

you know, i think that is working perfectly... thanks a lot

[smerny]

Could you explain the process/order at which the computer looks at your mysql query? I'd just like to get a better understanding so that I can more easily apply this in the future.

[smerny]

oh, and actually i just noticed a problem with it, it doesn't exclude topics that do not have the same ID_board as $id_board