Solved

FREETEXT and COUNT

Posted on 2006-11-20
3
387 Views
Last Modified: 2008-03-10
Hi I have a table that contains forum messages:

messageID       topicID       messageText

     1                    1           apple
     2                    1           orange
     3                    2           grape
     4                    1           cherry
     5                    3           peach

I have a query to get all the messages that contain the word "apple":

            SELECT * FROM tblMessages WHERE FREETEXT (messageText, 'apple');

This works fine but I would also like a count of how many other messages there are associated with the same topicID as the records returned by the the above query.  This is how I would like the result set to be:

messageID      topicID         messageText       numMsgs
      1                  1                 apple                   3

Is this possible?
0
Comment
Question by:champ_010
3 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 125 total points
ID: 17977687
SELECT
    tm.messageId,
    tm.topicId,
    (select count(*) from tblMessages as tm2 where tm2.topicId = tm.topicId) as numMsg
FROM
    tblMessages as tm
WHERE
    FREETEXT (tm.messageText, 'apple');
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17977707
SELECT
    tm.messageId,
    tm.topicId,
    No_Of_Msg
FROM
    tblMessages as tm
inner join (select count(*) No_Of_Msg , topicId from tblMessages group by topicId ) TC
on tc.topicId = tm.topicId
WHERE
    FREETEXT (tm.messageText, 'apple')
go
0
 
LVL 1

Author Comment

by:champ_010
ID: 17977773
Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now