Hi folks. I have a kind of complex situation and have tried several things but haven't gotten the exact results I need. Any suggestions you might have are welcome. Here's the scenario. (Keep in mind I'm on MX 6.1)
I have some information stored in a database in three tables: processes, tasks, and details. There are a total of about 900 rows of data. This data is inter-related to one another in the following ways: I have a list of 'processes' like this: 1. Repair an Ice Cream Maker 2. Repair a Toaster 3. etc. There are about 20 of these that I store in the collection. The only data stored is the name of the process itself as shown above. I also have a list of 'tasks'. These tasks are related to processes. For example, tasks in Process #1 above might be: 1. Disassemble unit. 2. Find broken part. 3. Replace broken part. 4. Reassemble unit. Now, keep in mind, that I have only ONE record in the task table that says 'Dissable unit.' but that particular record is related to more than one process records. through a relation table called 'process_task_relation'. I have the same situation with details of each task being related to tasks. One detail record could be related to multiple task records. Okay, now that you have an understanding of the database set up, here's my issue. I am trying to index this data into a collection for fast text searching. Basically, if I index everything as it is related to each other, I end up with about 4,000+ rows in the collection. Of course, this means that information is in the collection more than once (an occurrence of each task relation to a process means the task is in there that many times, and so on for details as well). CFSEARCHes run faster on a smaller collection, so I'm trying to figure out how to index only the individual rows and then expand them out on the search results page. So, what I am doing on my search results is that I run my CFSEARCH to get my matching individual records. Then, I use those 'keys' to pull the 'expanded' records from the database (e.g. Process only, Process + Task or Process + Task + Detail). So I end up with two query result sets: 1. result set from CFSEARCH which has the original search results with the scores based on the search criteria. 2. result set from database which has more than one record that relates back to each of the records in the search results. I am now trying to JOIN these two queries using a Query of Queries to join the SCORES of the original search results to the 'expanded' data found in the database. My ideal result would be a query with all expanded data (everything is related as it should be) and the score from the original process, task, or detail that was found in the search result related to the right records. So, for example, if the search criteria was found in a detail, and the score on that was say 78%, and if that search criteria was used in 4 different tasks that were each used on two different processes, then I should end up having 8 rows in my database with the same 78% score. I explained as much as possible just in case someone might think of another completely different approach to what I'm trying to do. Again, I am trying to avoid storing all 4,000+ records in the index, so, at this point, that is not an option. Thanks in advance for your suggestions or comments. Dave ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265920 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
