Re: Search by "AND"....

No i'm not going to find a word with contains both wordA and wordB.. I want to find an itemID that has both wordA AND wordB.. Create table item ( itemID ... .... ) CREATE TABLE word ( wordID int(10) unsigned NOT NULL auto_increment, word varchar(100) DEFAULT '' NOT NULL, PRIMARY KEY (wordID), UNIQUE word (word) ); CREATE TABLE word_item ( wordID int(10) unsigned DEFAULT '0' NOT NULL, itemID int(10) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (wordID,jobID) ); Any help? Thanks. Matthew Ballard wrote: > > 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 > > -- > 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