| Author |
 Topic  |
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
 Posted - 21 April 2002 : 11:21:43
|
But I guess you can change that:
select r_date , t_subject FROM FORUM_TOPICS LEFT JOIN FORUM_REPLY ON FORUM_TOPICS.TOPIC_ID = FORUM_REPLY.TOPIC_ID
WHERE r_DATE>'20020321201244' AND r_AUTHOR=1
UNION select t_date as r_date,t_subject from forum_topics
WHERE t_DATE>'20020321201244' AND t_AUTHOR=1
ORDER BY r_date DESC;
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
 |
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 11:22:45
|
The R_DATE and T_DATE gets put into one unioned field called R_DATE (it is named after the first stmt).
That new field called R_DATE is then sorted. The result is posts in date order whether they are topics or replies.
You don't need to change T_DATE to R_DATE since the UNION does that for us.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD
Edited by - aaron s. on 21 April 2002 11:23:52 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 15:00:00
|
Aaron,
Here is a query to get only non-duplicate records adequately sorted. It works. I've tested it:
SELECT DISTINCT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_REPLY.R_AUTHOR, (SELECT TOP 1 R_DATE FROM FORUM_REPLY WHERE (FORUM_REPLY.TOPIC_ID = FORUM_TOPICS.TOPIC_ID AND FORUM_REPLY.R_AUTHOR=5) ORDER BY R_DATE DESC) AS R_DATE
FROM FORUM_REPLY INNER JOIN FORUM_TOPICS ON FORUM_REPLY.TOPIC_ID=FORUM_TOPICS.TOPIC_ID
WHERE FORUM_REPLY.R_AUTHOR=5
UNION
SELECT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_AUTHOR, FORUM_TOPICS.T_DATE
FROM FORUM_TOPICS LEFT JOIN FORUM_REPLY ON FORUM_TOPICS.TOPIC_ID = FORUM_REPLY.TOPIC_ID
WHERE FORUM_TOPICS.T_AUTHOR=5 AND FORUM_REPLY.TOPIC_ID Is Null
ORDER BY R_DATE DESC;
Obviously as in the case you posted, this may need aditional columns. I one main advantage here: it doesn't return duplicate records and everything is done in SQL, so it should be faster than using code to filter duplicate records.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
|
|
Aaron S.
Average Member
USA
985 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 16:47:24
|
That's not true. It does include. I have several situations like those and the topics are shown. The date shown, however, is not the date of the last post made by the author. Gonna check that again.
I've tested this on my test forum and I can assure you all topics are shown. Maybe check your data again.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 21 April 2002 16:49:42 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 16:51:52
|
Ok. I've checked it and it does miss some topics that were started by the author and to which he didn't reply.
Gotta look at this again.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 19:04:43
|
SELECT DISTINCT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_REPLY.R_AUTHOR, (SELECT TOP 1 R_DATE FROM FORUM_REPLY WHERE (FORUM_REPLY.TOPIC_ID = FORUM_TOPICS.TOPIC_ID AND FORUM_REPLY.R_AUTHOR=5) ORDER BY R_DATE DESC) AS R_DATE
FROM FORUM_REPLY INNER JOIN FORUM_TOPICS ON FORUM_REPLY.TOPIC_ID=FORUM_TOPICS.TOPIC_ID
WHERE FORUM_REPLY.R_AUTHOR=5
UNION
SELECT DISTINCT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_AUTHOR, FORUM_TOPICS.T_DATE
FROM FORUM_TOPICS AS FORUM_TOPICS LEFT JOIN FORUM_REPLY AS FORUM_REPLY ON FORUM_TOPICS.TOPIC_ID=FORUM_REPLY.TOPIC_ID
WHERE (FORUM_TOPICS.T_AUTHOR=5 AND FORUM_REPLY.TOPIC_ID Is Null) OR (FORUM_TOPICS.T_AUTHOR=5 AND FORUM_REPLY.TOPIC_ID Not In (SELECT TOPIC_ID FROM FORUM_REPLY WHERE R_AUTHOR=5))
ORDER BY R_DATE DESC;
Well this takes care of the missing topics. Regarding the dates I'm beggining to think that it won't be possible to do it in a single SQL instruction. Uummm this is being harder than I expected...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 19:23:02
|
That's one heck of a SQL stmt!
I'm sure there is a way to get it to do it all in one stmt, but at some point it's like killing a bug with a grenade.
The pipe delimeted code seems to work fine. I'm going to stick 1000+ topics in my Db to see what happens to performance. If that is fine, then I'm satisfied.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 19:25:49
|
Here's the stmt that does it all:
SELECT DISTINCT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_REPLY.R_AUTHOR, (SELECT TOP 1 R_DATE FROM FORUM_REPLY WHERE (FORUM_REPLY.TOPIC_ID = FORUM_TOPICS.TOPIC_ID AND FORUM_REPLY.R_AUTHOR=1) ORDER BY R_DATE DESC) AS R_DATE
FROM FORUM_TOPICS INNER JOIN FORUM_REPLY ON FORUM_TOPICS.TOPIC_ID = FORUM_REPLY.TOPIC_ID
WHERE (((FORUM_REPLY.R_AUTHOR)=1) AND (((SELECT TOP 1 R_DATE FROM FORUM_REPLY WHERE (FORUM_REPLY.TOPIC_ID = FORUM_TOPICS.TOPIC_ID AND FORUM_REPLY.R_AUTHOR=1) ORDER BY R_DATE DESC))>"20020321010101"))
UNION
SELECT DISTINCT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_AUTHOR, FORUM_TOPICS.T_DATE
FROM FORUM_TOPICS LEFT JOIN FORUM_REPLY ON FORUM_TOPICS.TOPIC_ID = FORUM_REPLY.TOPIC_ID
WHERE (((FORUM_TOPICS.T_AUTHOR)=1) AND ((FORUM_TOPICS.T_DATE)>"20020321010101") AND ((FORUM_REPLY.TOPIC_ID) Is Null)) OR (((FORUM_TOPICS.T_AUTHOR)=1) AND ((FORUM_REPLY.TOPIC_ID) Not In (SELECT TOPIC_ID FROM FORUM_REPLY WHERE R_AUTHOR=1)))
ORDER BY R_DATE DESC;
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Aaron S.
Average Member
USA
985 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 19:46:03
|
Sorry Aaron, I don't think the change will give you the date for the last reply by the user. It will give you the date for the last post on the topic, for topics with replies by the user you are looking for, and that leaves us almost where we started.
I'd like this to work, but it's still not working...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 19:49:52
|
Hmmm...
It seems to be ordering the data in the correct order... and the same order as the bubble sort page & the first UNION page that I created.
I'll try it with more test data to see if I can find a problem.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
Topic |
|
Topic Locked
