| Author |
 Topic  |
|
|
ILLHILL
Junior Member
Netherlands
341 Posts |
 Posted - 23 February 2006 : 16:32:48
|
I'm trying on getting the last 3 topics to show up as links
in a person's post. So when an author writes something interesting
the reader can click on the last 3 topics started by this author.
However, the links show up but also contain the author's replies
instead of only his started topics.
I know it has to do with the Join part in here:
'## Forum_SQL - Find all records for the member
strsql = "SELECT F.FORUM_ID"
strSql = strSql & ", T.TOPIC_ID"
strSql = strSql & ", T.T_DATE"
strSql = strSql & ", T.T_SUBJECT"
strSql = strSql & ", T.T_MESSAGE"
strSql = strSql & ", T.T_STATUS"
strSql = strSql & ", T.T_LAST_POST"
strSql = strSql & " FROM ((" & strTablePrefix & "FORUM F LEFT JOIN " & strTablePrefix & "TOPICS T"
strSql = strSql & " ON F.FORUM_ID = T.FORUM_ID) LEFT JOIN " & strTablePrefix & "REPLY R"
strSql = strSql & " ON T.TOPIC_ID = R.TOPIC_ID) "
strSql = strSql & " WHERE (T_DATE > '" & strStartDate & "') "
strSql = strSql & " AND (T.T_AUTHOR = " & TMember_ID
strSql = strSql & " OR R.R_AUTHOR = " & TMember_ID & ")"
strSql = strSql & " AND (T_STATUS < 2 OR T_STATUS = 0)"
strSql = strSql & " AND F.F_TYPE = 0"
strSql = strSql & " ORDER BY T.TOPIC_ID DESC"
Everytime I experiment with the Left Join part it bugs out on me.
Can anybody show me the way?
Thanks in advance, Dominic
< |
CLPPR.com - All The News Only Seconds Away |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 23 February 2006 : 17:03:39
|
If I'm remembering right, the initial post is in the topics table and all replies are in the reply table... if that's right then I'd think you wouldn't need the reply table in the query to show just the topics they started....
But I'd get some confirmation on that first part before I took my word for it... I'm not exactly working at 100% today.< |
 |
|
|
ILLHILL
Junior Member
Netherlands
341 Posts |
|
|
ILLHILL
Junior Member
Netherlands
341 Posts |
Posted - 24 February 2006 : 20:56:20
|
I tried this:
'## Forum_SQL - Find all records for the member
strsql = "SELECT F.FORUM_ID"
strSql = strSql & ", T.TOPIC_ID"
strSql = strSql & ", T.T_DATE"
strSql = strSql & ", T.T_SUBJECT"
strSql = strSql & ", T.T_STATUS"
strSql = strSql & ", T.T_LAST_POST"
strSql = strSql & " FROM " & strTablePrefix & "TOPICS T"
strSql = strSql & " WHERE T_DATE > " & strStartDate
strSql = strSql & " AND T.T_AUTHOR = " & TMember_ID
strSql = strSql & " AND T_STATUS < " & 2
strSql = strSql & " ORDER BY T.TOPIC_ID DESC"
But no luck.
Anybody knows how to do this?
Thanks, D< |
CLPPR.com - All The News Only Seconds Away |
|
|
|
Etymon
Advanced Member
United States
2385 Posts |
Posted - 26 February 2006 : 00:04:09
|
In your SELECT statement, you'll have to add the term TOP 3 so the query will select the top (or first) 3 in the order you specify (either ascending or descending).
It needs to looks something like this:
strSql = "SELECT TOP 3 TOPIC_ID FROM " & strTablePrefix & "TOPICS WHERE T_AUTHOR = " & TMember_ID & " ORDER BY TOPIC_ID DESC"
or
strSql = "SELECT TOP 3 T.TOPIC_ID FROM " & strTablePrefix & "TOPICS T WHERE T.T_AUTHOR = " & TMember_ID & " ORDER BY T.TOPIC_ID DESC"
This query is assuming that you are seeking the first three most recent topic id's created by the author id carried in TMember_ID.< |
Edited by - Etymon on 26 February 2006 00:16:24 |
|
|
|
ILLHILL
Junior Member
Netherlands
341 Posts |
Posted - 26 February 2006 : 13:47:59
|
Got it to work! Thanks so much!
One last thing. Where do I add the T.T_SUBJECT part, so I can display the topic titles as
links?
Greets & thanks again!
Dominic< |
CLPPR.com - All The News Only Seconds Away |
|
|
|
Etymon
Advanced Member
United States
2385 Posts |
Posted - 27 February 2006 : 06:22:10
|
If your Record Set is called rs, then after you open it use
rs("T_SUBJECT")
where ever you need it or declare it, as in
Topic_Subject = rs("T_SUBJECT")
and then use
Topic_Subject
where ever you may need it.  < |
|
|
|
ILLHILL
Junior Member
Netherlands
341 Posts |
Posted - 27 February 2006 : 14:07:52
|
It gives an error when I try it:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
Shouldn't the T.T_SUBJECT be included in the string somewhere?< |
CLPPR.com - All The News Only Seconds Away |
|
|
|
ILLHILL
Junior Member
Netherlands
341 Posts |
Posted - 27 February 2006 : 14:16:47
|
Nevermind, solved it:
strSql = "SELECT TOP 3 T.TOPIC_ID, T.T_SUBJECT FROM " & strTablePrefix & "TOPICS T WHERE T.T_AUTHOR = " & TMember_ID & " ORDER BY T.TOPIC_ID DESC"
set rs2 = my_Conn.Execute(strsql)
Thanks so much!
Greets, Dominic
< |
CLPPR.com - All The News Only Seconds Away |
|
|
| |
Topic |
|
Author's Last 3 Topics In Topic.asp
