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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to