| Author |
 Topic  |
|
Podge
Support Moderator
Ireland
3776 Posts |
 Posted - 20 April 2005 : 21:12:08
|
I need to get the total amount of topics and replies in a forum which I can do using the following two sql queries.
SELECT Count(T_Status) AS Expr1 FROM FORUM_TOPICS WHERE FORUM_TOPICS.CAT_ID=2 AND FORUM_TOPICS.FORUM_ID=5
SELECT Sum(T_Replies) AS Expr1 FROM FORUM_TOPICS WHERE FORUM_TOPICS.CAT_ID=2 AND FORUM_TOPICS.FORUM_ID=5
I add the results together and it returns the correct amount of total posts in the forum but I can help thinking there's a better way.
Anyone know how I can do it with one query?
|
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 April 2005 : 21:16:51
|
SELECT Count(T_Status) AS MyCount, Sum(T_Replies) as MySum FROM FORUM_TOPICS WHERE FORUM_TOPICS.CAT_ID=2 AND FORUM_TOPICS.FORUM_ID=5
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 20 April 2005 21:17:09 |
 |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
Posted - 21 April 2005 : 09:12:03
|
I have a few more if your up to it.
I need a query which will return the author's name instead of id in a query.
SELECT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_AUTHOR FROM FORUM_TOPICS
and I intend to use something like this to get the authors name.
SELECT FORUM_MEMBERS.MEMBER_ID, FORUM_MEMBERS.M_NAME, FORUM_MEMBERS.M_USERNAME FROM FORUM_MEMBERS WHERE FORUM_MEMBERS.MEMBER_ID=1
Is there any way to return the authors name in the first query?
Incidentally, which is the authors name ? M_NAME or M_USERNAME ? |
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
Edited by - Podge on 21 April 2005 09:12:41 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2005 : 10:22:58
|
| The authors name is M_NAME. You need to add a INNER JOIN to your first query, to the members table, joining T_AUTHOR with MEMBER_ID. Can you do it or do you need further help? |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 25 April 2005 : 08:30:39
|
Or you could use:
SELECT T.TOPIC_ID,T.T_SUBJECT,T.T_AUTHOR,M.M_NAME FROM FORUM_TOPICS T,FORUM_MEMBERS M WHERE T.T_AUTHOR=M.M_NAME
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
Podge
Support Moderator
Ireland
3776 Posts |
Posted - 28 April 2005 : 11:33:38
|
Thanks Shaggy & Ruirib. I'm nearly there with this query. What I have now is this
SELECT T.TOPIC_ID, R.TOPIC_ID, R.REPLY_ID, T.T_SUBJECT, T.T_MESSAGE, R.R_MESSAGE, T.T_AUTHOR, R.R_AUTHOR, R.R_DATE, T.T_DATE
FROM FORUM_TOPICS T LEFT JOIN FORUM_REPLY R ON T.TOPIC_ID = R.TOPIC_ID where T.FORUM_ID=2
I need to add M.M_NAME to the SELECT part (easy) but don't know where to add the FORUM_MEMBERS M e.g.
SELECT T.TOPIC_ID, R.TOPIC_ID, R.REPLY_ID, T.T_SUBJECT, T.T_MESSAGE, R.R_MESSAGE, T.T_AUTHOR, R.R_AUTHOR, R.R_DATE, T.T_DATE, M.M_NAME
FROM FORUM_MEMBERS M, FORUM_TOPICS T LEFT JOIN FORUM_REPLY R ON T.TOPIC_ID = R.TOPIC_ID where T.FORUM_ID=2
I think it may be possible with a nested select query or is there another way?
Also, is it possible to combine the two date columns R.R_DATE & T.T_DATE so that there is only one date column with the most recent date? If not, I can get around that programatically.
|
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
|
Podge
Support Moderator
Ireland
3776 Posts |
Posted - 28 April 2005 : 13:37:09
|
Actually, that query won't cut it either.
It might be easier if I explain exactly what I need.
A list of all individual posts in a forum given the forum id (e.g. topics and replies being treated equally as posts)
I need the following columns
Subject | Author Name | topic or reply date | Topic or Reply ID |
Subject should be the topic subject if the row is a topic and the original topic subject if a reply.
I have tried a left join but some topics & replies are left out e.g. it matches a topic and reply into one row.
The query would be like a union between these two queries run seperately (union doesn't work either - some replies get left out)
SELECT FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_AUTHOR, FORUM_TOPICS.T_DATE, FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.FORUM_ID
FROM FORUM_TOPICS
WHERE FORUM_TOPICS.FORUM_ID=1
SELECT FORUM_REPLY.R_DATE, FORUM_REPLY.TOPIC_ID, Null AS Expr1, FORUM_REPLY.R_MESSAGE, FORUM_REPLY.R_AUTHOR, FORUM_REPLY.FORUM_ID
FROM FORUM_REPLY
WHERE FORUM_REPLY.FORUM_ID=1
|
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 28 April 2005 : 14:27:17
|
You do need a UNION query:
SELECT FORUM_TOPICS.T_SUBJECT, FORUM_MEMBERS.M_NAME, FORUM_TOPICS.T_DATE, FORUM_TOPICS.TOPIC_ID
FROM FORUM_TOPICS, FORUM_MEMBERS
WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_TOPICS.T_AUTHOR) AND FORUM_TOPICS.FORUM_ID=1
UNION
SELECT FORUM_TOPICS.T_SUBJECT, FORUM_MEMBERS.M_NAME, FORUM_REPLY.R_DATE, FORUM_REPLY.REPLY_ID
FROM FORUM_REPLY, FORUM_MEMBERS, FORUM_TOPICS
WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_REPLY.R_AUTHOR) AND (FORUM_TOPICS.TOPIC_ID=FORUM_REPLY.TOPIC_ID) AND FORUM_TOPICS.FORUM_ID=1
Order By R_DATE;
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
Posted - 28 April 2005 : 15:37:35
|
Thanks Ruirib for your help so far, I wouldn't be anywhere near completing this without you.
I changed the query slightly and it works in Access but not Sql Server
SELECT FORUM_TOPICS.T_SUBJECT, FORUM_MEMBERS.M_NAME, FORUM_TOPICS.T_DATE, (FORUM_TOPICS.TOPIC_ID & '_0') as ART FROM FORUM_TOPICS, FORUM_MEMBERS WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_TOPICS.T_AUTHOR) AND FORUM_TOPICS.FORUM_ID=1 UNION SELECT ('Re: ' + FORUM_TOPICS.T_SUBJECT), FORUM_MEMBERS.M_NAME, FORUM_REPLY.R_DATE, (FORUM_REPLY.TOPIC_ID & '_' & FORUM_REPLY.REPLY_ID) FROM FORUM_REPLY, FORUM_MEMBERS, FORUM_TOPICS WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_REPLY.R_AUTHOR) AND (FORUM_TOPICS.TOPIC_ID=FORUM_REPLY.TOPIC_ID) AND FORUM_TOPICS.FORUM_ID=1
ORDER BY T_DATE;
I added Re: to the subject for replies (no problem there)
In order to give each post (topic or reply) a unique id of their own I combined the topic id & "_" & reply id e.g. 1_14 would denote topic 1 reply 14. This works fine in access but not in Sql Server.
I can see three ways to solve this problem;
1. Give each row a running count which will act as a unique id BUT later in order to find the row the query would have to be run again (expensive cpu-wise)
2. Use casting to change the column to varchar for sql server. The benefit would be that when I need to retrieve the post I can split the 1-14 and retrieve the post easily and efficiently.
3. You have a better idea  |
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
|
Topic |
|
Topic Locked
