Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

FREETEXT and COUNT

Posted on 2006-11-20
3
Medium Priority
?
443 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 500 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

581 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