The Forum has been Updated
The code has been upgraded to the latest .NET core version. Please check instructions in the Community Announcements about migrating your account.
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
<
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
<
Postet den
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.<
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.<
Postet den
Any idea how I would do this? The moment I start messing with it it results in:
Syntax error in JOIN operation
<
Syntax error in JOIN operation
<
Postet den
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<
'## 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<
Postet den
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.<
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.<
Postet den
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<
links?
Greets & thanks again!
Dominic<
<