That makes sense on the DISTINCT trying to act across all the rows since I was
using an * to pull in data from the tables. Thanks for the order by tip,
nesting them like that should work for what I'm needing. But I'm still
confused on if I need to filter out the results so that thread's with several
search hits don't show multiple times. Does the GROUP command create something
like a ValueList of distinct values without repeats?
let me give you an example data set to try and explain:
Forum_ID Thread_ID Post_ID Thread_Title Post_Content
1 1 1 Thread #1 This is a test
1 2 2 Thread #2 This is another test
1 1 3 Thread #1 Reply to "this is a test"
2 3 4 Thread #3 test in a new 'forum' area
If searching for 'test' I'd like to return the results:
Matches found: 4 in 3 threads
Forum "1":
Thread #1 (table with author, lastpost date, etc pulled from 'select *' in
search)
Thread #2 (similar table info..)
Forum "2":
Thread #3 (similar table info..)
I'll play around with trying to group the results as you described, but I was
under the impression I needed to somehow filter or comibine the common
thread_id's prior to displaying the group?
Thanks for the help.
>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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Save $10 Download ZoneAlarm Security Suite
http://www.houseoffusion.com/banners/view.cfm?bannerid=66
Message: http://www.houseoffusion.com/lists.cfm/link=i:15:1083
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
