Good day, I have a query that is working however I am having trouble getting one more value in my recordset. My objective here is to pull the required info in one query. It is a page currently getting five records, I would like to get a sixth. The two tables being addressed have the following structure (SQL Server 2000):

1..ForumCategorys: (PK=id) id,perId.posId,created,fOrder,catName,catDescripti on,inactive

2..ForumThreads:(PK=id, FK=catId)
id,perId,posId,projId,catId,datePosted,topicTitle, topicParent,thread

The following query works, it pulls five records and is pasted in here from query analyzer:
SELECT ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription,
COUNT(ForumThreads.id) AS totalThreads,
MAX(ForumThreads.datePosted) AS Posted
FROM ForumCategorys,ForumThreads
WHERE ForumCategorys.inactive=0
AND ForumThreads.ProjID =9
AND ForumThreads.CatID = ForumCategorys.ID
GROUP BY ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription
ORDER BY ForumCategorys.catName

I would like to get the posId (positionId) of the most recent post. The most recent post being MAX(ForumThreads.datePosted) as seen in the above query.

TYIA


Wind is your friend
Matt

__________________
Wind is your friend
Matt

Hi Mat,

to get that extra field in there you need to apply either of the techniques in this recent post http://p2p.wrox.com/topic.asp?TOPIC_ID=25561 to the ForumThreads table and then join that subquery to the ForumCategorys table.

hth
Phil

pgtips

Thank you for your time, I got it working using:
SELECT ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription, COUNT(ForumThreads.id),MAX(ForumThreads.datePosted ),position.shortTitle FROM ForumCategorys,ForumThreads,position WHERE ForumCategorys.inactive=0 AND ForumThreads.ProjID =9 AND ForumThreads.CatID = ForumCategorys.ID AND ForumThreads.PosId = Position.posID GROUP BY ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription,position.shortTitle ORDER BY ForumCategorys.catName;

However there is a problem, if there is no entries in the ForumThreads table using this query it fails to show categories that have no topics. Even if there are no topics it should show the categroy and state there are 0 topics. Anyhow I have used two queries and reached my objective. A I said, trying to minimize the amount of queries was my aim.

TYVM

Wind is your friend
Matt

use a LEFT OUTER JOIN instead of putting the join in the where clause

aaggghhh I see. I am on a different machine at the moment but will try it tommorow. Thanking you kindly.

Wind is your friend
Matt

I changed it a couple of different ways however still no record for a category that has not topics/threads under it (entries in the ForumThreads tbl) You were suggesting using a the LEFT OUTER JOIN in the following fashion?

SELECT ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription, COUNT(ForumThreads.id),MAX(ForumThreads.datePosted ),position.shortTitle FROM ForumThreads LEFT OUTER JOIN ForumCategorys ON ForumCategorys.id = ForumThreads.catId LEFT OUTER JOIN Position ON position.posId = ForumThreads.posId WHERE ForumCategorys.inactive=0 AND ForumThreads.ProjID =9 GROUP BY ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription,position.shortTitle ORDER BY ForumCategorys.catName;



Wind is your friend
Matt

when you use a left or right join it matters which way around your tables are.

So instead of:
FROM ForumThreads LEFT OUTER JOIN ForumCategorys ON ForumCategorys.id = ForumThreads.catId

use:
FROM ForumCategorys LEFT OUTER JOIN ForumThreads ON ForumCategorys.id =
ForumThreads.catId

rgds
Phil