Query help!

[JeroenDortmans]

I have the following tables:

TABLE FORUMS
forum_id
forum_name
forum_description
active

TABLE TOPICS
topic_id
forum_id
topic_title
topic_user_id
topic_views
topic_replies
topic_last_post
topic_text
topic_image_name
topic_replayto

TABLE USERS
user_id
username
user_password
user_posts
user_email
user_website
user_country
user_city

Now I have the following query:

SELECT DISTINCT forums.forum_id, forums.forum_name, forums.forum_description, topics.topic_id, topics.topic_user_id, topics.topic_last_post, flaneren_users.username
FROM forums, topics, flaneren_users
WHERE topics.topic_user_id = flaneren_users.user_id AND forums.forum_id = topics.forum_id AND forums.active = 1
GROUP BY forums.forum_id
ORDER BY topics.topic_last_post DESC

The problem with this query is that it doesn't returns the last posting (topics.topic_last_post).
When I make a query with MAX(topics.topic_last_post) it returns the last posting, but the topics.topic_user_id isn't corresponding with the last date returned by MAX(topics.topic_last_post).

How can I make this query work correct?????

 

[rac2]

I wonder what you are trying to do with your query. Knowing that would help folks assist you.


General observations.

A GROUP BY clause is normally used when aggregating data. I don't see any SUM(), MAX(), or COUNT() functions in your SELECT list. The absence of any aggregate functions suggests that you don't really mean to GROUP BY.

A GROUP BY clause will give you distict rows, generally there is no reason to use DISTINCT with GROUP BY.

If you simply need to know which user made the last post you could get that information like so.

SELECT 
       topics.topic_user_id,
       et cetera
FROM ...
ORDER BY topics.topic_last_post DESC

The first row has the answer.
If you want one row then use the equivalent of SQL Server TOP 1

SELECT TOP 1
       topics.topic_user_id,
       et cetera
FROM ...
ORDER BY topics.topic_last_post DESC

Maybe you want to see who made the last post to every topic.

SELECT 
       topics.topic_user_id,
       LastPosts.date_last_post
FROM topics
JOIN (
      SELECT
            topic_id,
            MAX(topic_last_post) AS "date_last_post"
      FROM topics
      GROUP BY topic_id
     ) LastPosts ON
   topics.topic_id = LastPosts.topic_id

Hope this helps.

 

[JeroenDortmans]

I will explain what I want with this query.

From the table forums I want to read out all the records which have "active" set as 1.

From table topics I want to read out the last post "topic_last_post" which must be done for every "forum_id" which is received from the table "forums". I also want the "topic_user_id" from this last post.

From table users I want to have the username which corresponds with "topic_user_id" so that I can display the username instead of the id number of the user.

Hope this clears my problem.