Re: Search by "AND"....

Are you sure that at least one of the word records in table2 contain both wordA and wordB (or whatever they may be), and that there is a corresponding wordID in table1? Try: select wordID from table2 where word like '%wordA%' and word like '%wordB%'; and see if it returns any records, then try with the relational part of the query. I tried an almost identical query, just with different tables, and it worked just fine. That is the most logical way I can think of to do what you want (assuming you do want both words to be part of word). Matthew At 03:13 PM 12/13/2000 +0800, you wrote:

I got two typical MySQL tables for fulltext search:

table1        table2
------        ------
wordID        wordID
itemID        word

where "word" holds all words ------------------------------------------------------ When I try to query the database for any items with EITHER wordA OR wordB, I do the following: mysql> select itemID from table1,table2

        where table1.wordID=table2.wordID
        and (word like '%wordA%' or word like '%wordB%');

It works. But when I want query for any items which contain BOTH wordA AND wordB, the following query always outputs 0 rows. mysql> select itemID from table1,table2

        where table1.wordID=table2.wordID
        and (word like '%wordA%' and word like '%wordB%');

Why? And what's the simplest way to accomplish my task? Many thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net