DISTINCT acts across all returned rows.
A quick way to output what you want is to use the group attribute in
cfoutput.
<CFQUERY NAME='keywordsearch' ...>
SELECT (needed fields)
FROM (proper tables)
WHERE post_content LIKE'%#i#%'
ORDER BY ThreadID
</CFQUERY>
<cfoutput query="keywordsearch" group="ThreadID">
#keywordsearch.ThreadID#<br />
</cfoutput>
If you then want to group by another column, forum_id, then try this.
<CFQUERY NAME='keywordsearch' ...>
SELECT (needed fields)
FROM (proper tables)
WHERE post_content LIKE'%#i#%'
ORDER BY FieldID, ThreadID
</CFQUERY>
<cfoutput query="keywordsearch" group="FieldID">
<cfoutput group="ThreadID">
#keywordsearch.ThreadID#<br />
</cfoutput>
</cfoutput>
The syntax is probably off with that last bit of code, have a play about
with it and see what you come up with.
Ade
-----Original Message-----
From: RammaR [mailto:[EMAIL PROTECTED]
Sent: 03 June 2005 11:31
To: CF-Newbie
Subject: Distinct query of queries trouble
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>
Oh, and secondly I'm going to also want to group them by the forum_id field
when displaying, but that should be a GROUP BY at the end of the query
correct?
Any tips or input would be greatly appreciated,
thanks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Protect your mail server with built in anti-virus protection. It's not only
good for you, it's good for everybody.
http://www.houseoffusion.com/banners/view.cfm?bannerid=39
Message: http://www.houseoffusion.com/lists.cfm/link=i:15:1082
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
