Complex query question

[marcucio]

For some reason I can't figure out the following. I am using mysql and php.

I have the following 2 tables

tl_topic //a topic
topic_id //id of the topic
title //the title of the topic
comment //the body of the topic
user_id //the user who posted the topic

tl_topic_comment //comments on a topic
topic_id
comment_id

both topic_id and comment_id are exact date/times that it was added.

I want to get a list of topics ordered by latest comment date (comment_id), and if there are no comments on a particular topic I need to use the topic_id to order it

Any help would be appreciated
Thanks

[marcucio]

I want to get a list of topics ordered by latest comment date (comment_id), and if there are no comments on a particular topic I need to use the topic_id to order it

Let me just be clear what I am asking for, I am just looking for the mySQL SELECT statement which would give me all the topics in the correct order.

I was trying to do it with temperary tables and joins but I think there might be an easier way

The first topic would be the newest one added or the one with the latest comment

Thanks Again

[debasisdas]

Kindly post your query for reference of our experts.

[marcucio]

well right now I have:

[PHP]$sql = "SELECT tl_topic.topic_id, tl_topic.title, tl_topic.user_id FROM tl_team_topic, tl_topic WHERE tl_team_topic.team_id = '$team_id' AND tl_team_topic.topic_id = tl_topic.topic_id ORDER BY tl_topic.topic_id DESC"[/PHP]

and this gets all the topics in the right order if none had comments. but I want to sort by topic_id and the latest comment on each topic. If there are no comments we just use topic_id but if there comments we need to sort by comment_id.

see the posts above to see the tables i'm working with

thanks

[amitpatel66]

For some reason I can't figure out the following. I am using mysql and php.

I have the following 2 tables

tl_topic //a topic
topic_id //id of the topic
title //the title of the topic
comment //the body of the topic
user_id //the user who posted the topic

tl_topic_comment //comments on a topic
topic_id
comment_id

both topic_id and comment_id are exact date/times that it was added.

I want to get a list of topics ordered by latest comment date (comment_id), and if there are no comments on a particular topic I need to use the topic_id to order it

Any help would be appreciated
Thanks

Try this:

 
SELECT tt.user_id,tt.title,tt.comment,tc.topic_id,tc.comment_id FROM tl_topic tt,tl_topic_comment tc WHERE tt.topic_id = tc.topic_id
ORDER BY IFNULL(tc.comment_id,tc.topic_id);