I have 2 tables books and booknames

when I entery a query of

SELECT booknames.book, books.chapter, books.verse, books.text FROM books INNER JOIN booknames ON books.book = booknames.book;

which returns 22827 rows

if I do select * from books where text LIKE '%apples%' AND text LIKE '%oranges%';

I get 9 rows

however if I do
SELECT booknames.book, books.chapter, books.verse, books.text FROM books INNER JOIN booknames ON books.book = booknames.book from books where text LIKE '%apples%' AND text LIKE '%oranges%';

I get Empty set.

What am I missing. What I am ultimately trying to do is allow my users to enter boolean style keyword searches .

btw if I remove
AND text LIKE '%oranges%';
or change it to
OR text LIKE '%oranges%';
Then my query works.

I am running mysql 4.0.18
thanks for your help.

1) If you know the math behind joins, then you will see why you can get 22k rows using one join method and no results using a different method.

2) You don't need to specify table.attribute for everything you are selecting, only the attributes which repeat in both tables need to be specified as such.

3) What language are you using for the interface?

I you did write your code exactly like this, you will need to remove the from-statement after your on-statement.

/pekron