[SOLVED] no freaking clue

[dadamssg]

Ok....deep breath....I have two tables. One for forum topics with the structure

 

title  varchar(100)

created_by  varchar(20)

create_date  datetime

topic_id  int(11)

content  text

views  int(11)

replies  int(11)

last_user  varchar(20)

last_time  datetime

 

and one that holds the responses to those topics

 

created_by  varchar(20)

create_date  datetime

topic_id  int(11)

response_id  int(11)

content  text

 

what i want to do is create a query that pulls up the topics that you've responded to BUT someone else posted a response since yours. Exactly like "Show new replies to your posts." link on this forum. any clues?

[kickstart]

Hi

 

Not sure how much of the topics you want, but this should get you the topic ids:-

 

SELECT DISTINCT a.topic_id
FROM responses a
INNER JOIN (
SELECT topic_id, MAX(create_date) AS maxCreateDate
FROM responses
WHERE created_by = '$MyName'
GROUP BY topic_id ) b
ON a.topic_id = b.topic_id
AND a.create_date > b.maxCreateDate

 

Might well be a more efficient way to do it  but I cannot see it at the moment.

 

All the best

 

Keith

[dadamssg]

wow...that was impressive. It's almost what i want. This pulls up all the responses after a certain user's response. What i want is it to select the Topics. So....this pulls up the correct topic_id numbers...but i want it to pull these out of the Topic table so i can have all the info in the Topic rows. Does that make sence?

[dadamssg]

SOLVED...thanks again kickstart!

 

SELECT * FROM Topics WHERE topic_id IN (SELECT DISTINCT a.topic_id
FROM Responses a
INNER JOIN (
SELECT topic_id, MAX(create_date) AS maxCreateDate
FROM Responses
WHERE created_by = '$userr'
GROUP BY topic_id ) b
ON a.topic_id = b.topic_id
AND a.create_date > b.maxCreateDate)

[dadamssg]

new question...same tables and what not

 

how would i write a query for the main forum board that would pull up all topics and arrange them by activity. I.e. the topic that has the most recent responses gets listed first? any clue???

[dadamssg]

nevermind....forgot i had the last_time field...i'm stupid