Verity Update Query

[jchastai]

I am interested in figuring out how to build an update query for a verity search for a discussion forum app.

My situation is that I have a table called threads that has a threadID and threadName. I have a table called messages with a messageID, threadID, and messageText.

What I think I want as a result is a query that returns all of the threads - threadID and threadName - as well as a concatenated list of messageText's with the messageText of each message contained in that thread.

This way I can run a verity search against the messageText field and return individual threads (no duplicates) that contain the searched for text, rather than returning individual messages.

I am baffled by how to go about this, so does anybody have a suggestion as to how to do this, or does anybody know of a better method?

Thanks
Jeff Chastain

 

[guljar]

If I understand rightly you want your result of query as
colums ThreadID, ThreadName and ConcatedMsg. But by no query you can do this. So if you only want ThreadID and ThreadName then following query will do it,
select ThreadID, TheadName from Threads where ThreadID in(select ThreadID from Messages where [your messagetext search criteria])

But if you want the first case you must have to use some reporting tools.