Re: DISTINCT in SQL query

Try with SELECT distinct title,filelocation,filename,ranking,summary FROM pdf LEFT JOIN searchwords USING(id) LEFT JOIN words USING(id) WHERE LCASE(words.word) LIKE LCASE('%$searchstr%') ORDER BY ranking DESC; Have in mind that all text fields in MySQL except with added clause BINARY, or BLOBs ARE CASE_INSENSITIVE It is better to JOIN your tables with LEFT JOIN, or RIGHT JOIN but don't expect that MySQL will optimize your query well when you do this kind of cartesian join. Try with 4 tables everyone with 200,000 rows and you will see the diff. May be without LEFT(RIGHT) JOIN your mysqld will fork too much. Andrey Hristov IcyGEN Corporation http://www.icygen.com 99% ----- Original Message ----- From: "Scott Mebberson" <scott@redbluedesign.com> To: <php-db@lists.php.net> Sent: Monday, August 27, 2001 9:33 AM Subject: [PHP-DB] DISTINCT in SQL query > Hi Guys, > > I am retriving information based across three tables, here is the SQL > statement > > SELECT DISTINCT title, filelocation, filename, ranking, summary FROM pdf, > words, searchwords WHERE words.id = searchwords.word_id AND pdf.id = > searchwords.pdf_id AND LCASE(words.word) LIKE LCASE('%$searchstr%') ORDER BY > ranking DESC; > > The only problem is, the return set has duplicated rows. But I think I may > have a problem in the words table. I am indexing pdf files and they each > have upto 15 search words, but some of the rows in the words table aren't > words, but instead three or four words, ie. > > script (this is a word in one row) > server script (these words are in the second row) > > So the query is picking up script in both cases due to the %% in the LIKE > clause, is there anyway to remove the duplicate rows or do I have to have > only one word in each row in the words table? > > Hope this makes sense... let me know if it doesn't and I'll re-write it ;)\ > > Thanks > > Scott. > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > >