Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: I am not understanding whats going wrong in my query

    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.
    Last edited by qa-guy; 03-12-04 at 03:30.

  2. #2
    Join Date
    Sep 2003
    Posts
    69
    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?

  3. #3
    Join Date
    Mar 2004
    Posts
    8

    Re: I am not understanding whats going wrong in my query

    Originally posted by qa-guy
    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.
    I you did write your code exactly like this, you will need to remove the from-statement after your on-statement.

    /pekron

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •