I'm trying to generate a search results page for a simple forum type page. My problem is that I'm searching the post content for the inputed keywords and then return a distinct list of the threads because often there are mutliple search hits within a common thread. So what I've done is a main query for the search with then pulls in all of the user/thread/forum data. Then do a 'distinct' query of queries on those results to filter out the multiple thread listings. So i might get 15 hits in 10 threads, which is working fine. However when I go to display the results I'm having trouble using that distinct list, everything I'm trying seems to show all 15 with repeats instead of 10.

for this example the query structure is like this:

<CFQUERY NAME='keywordsearch' ...>
SELECT (needed fields)
FROM (proper tables)
WHERE post_content LIKE'%#i#%'
</CFQUERY>

<CFQUERY DBTYPE = "query" NAME="thread_count_query">
SELECT DISTINCT thread_ids
FROM keywordsearch
</CFQUERY>

<CFQUERY NAME="populate_results_list" DBTYPE="query" MAXROWS="#records_per_page#">
<trying to determine what fits here, originally:>
SELECT DISTINCT unique_thread_ids, *
FROM keywordsearch
</CFQUERY>

Any tips or input would be greatly appreciated,
thanks

Hi, there. I tried the same concept with some tables in my database.


Inside a, I'd get 9 records, 5 records from Journal1 with the keyword "the", and 4 records from Journal2 with the keyword "the".
After that, with select distinct a.jid from a, I get the ids for Journal1 and Journal2.
Basically, it's working for me.