Re: Join on single table

Sounds like a self join should work wonders. I didn't test this, but the idea should work: select t1.app_id as a1, t2.app_id as a2, t2.word, t2.score from tablename as t1 left join tablename as t2 on t1.resource_id = t2.resource_id and t1.app_id != t2.app_id group by word order by word voila, no temp tables. Am I missing something? -Micah On Thursday 10 February 2005 07:56 pm, Matthew Weier O'Phinney wrote: > I have a table which contains the following: > id (primary key, auto incrementing) > app_id (integer, foreign key) > resource_id (integer, foreign key) > word > score > > (This is a search index.) I want to find all resource_ids from one app_id > that match resource_ids in another app_id by word. > > I have created a temporary table 'tmp1' that contains all resource_ids > from the second app_id (the one whose resources I wish to retrieve). I > am then looping through all resource_ids in the main table with the > first app_id, and doing the following: > > * Creating a temporary table tmp2 with a single column 'word' > populated by the words associated with resource_id in the main > table > * Selecting all distinct resource_ids from tmp1 INNER JOIN'd on tmp2 > on the word field > > The issues I'm running into are that (1) each resource_id cycle takes a > good amount of time, and (2) I seem to be getting either too many > resource_ids or not enough. > > (1) may be something I just have to deal with. As it is, I'm planning on > running the full indexing once, and then doing incremental updates, so > it may not be that big of an issue (unless it takes too much time to > create the initial index). As for (2), unfortunately, I'm not sur ehow > to really trouble shoot the issue. I know, for instance, that in once > case, I have a list of 343 words that generates a list of ~12,000 > resource_ids (of a possible 18,000) -- but I don't quite know how to > spot check 300 values to be certain that this is reasonable. > > In a previous incarnation of the script, I was looping through each word > of each resource_id and then selecting out of tmp1 based on the single > word value. The results were very different (very few matches), and, > again, the script ran long. > > Any ideas? > > -- > Matthew Weier O'Phinney | WEBSITES: > Webmaster and IT Specialist | http://www.garden.org > National Gardening Association | Paìÿ|5 > $2›·žnñŒbhttp://www.kidsgardening.com > 802-863-5251 x156 | > http://nationalgardenmonth.org > mailto:matthew@garden.org | UåÁ÷$­ZÛ€‰ > X¡¾/http://vermontbotanical.org