massive query

[Julius]

Hi,

 

I have 5 tables:

 

categories

      id, name

forums

    id, cat_id, name, description

topics

    id, forum_id, name, date, user_id

posts

    id, topic_id, post, date, user_id

users

    id, nick, password

 

im trying to make a forum. on the main page of it, i want to show category name(will be at least 3 categories), forum name and description, latest post in that forum author name and topic name where post was posted. I want to make as less queries as i can. so, can anyone help me with that? I need: name from categories table, name from forums table, name from topics table, latest post user_id from posts table, nick from users table where id=user_id (from posts). can this all be retrieved with one query?

[fenway]

And what do you have so far? Nothing?

[Julius]

what do you mean? i have heard about 'join', googled for it, found some examples, tried, but they don't do exactly what I want. I dont want you to write the whole query for me, just give me a hint. what i tried:

SELECT categories.name, forums.name, topics.name,
posts.date, users.nick
FROM categories LEFT JOIN (forums, topics, posts, users)
ON (forums.cat_id=categories.id AND topics.fid=forums.id AND posts.tid=topics.id AND users.id=posts.author)

i think this might do the trick after some modifications, am I thinking right? this query takes only one forum (but there are three of them).

[fenway]

Yes, that's the right idea -- though the part about the most recent topic/post might be better as summary values, for efficiency.

[awjudd]

Wow ... is that a LEFT JOIN of the CROSS JOIN of 4 tables?  As fenway said, you probably want to optimize that for efficiency purposes.

 

~awjudd