Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Nov 2012
    Posts
    115
    Thanks
    7
    Thanked 12 Times in 12 Posts

    Searching data from two tables

    Hey guys,
    A bit late, but happy new year to all!

    Right, I've been searching all night now, and still can't find a definite answer to my problem.

    I've got 2 tables:

    Table: A
    Fields: id, division, description

    Table: D
    Fields: id, name

    D holds a list of divisions, A holds a list of things which are part of a division. Hence A.division is a foreign key to D.id

    For a normal SELECT statement I would do
    Code:
    SELECT A.*, D.name
    FROM A, D
    WHERE A.division = D.id
    AND id < 10  (for purpose of question only)
    This neatly returns the records I'm looking for.

    But, now I am creating a search function, which would grant the user the option to search for a specific term. This term could be either part of A.description OR D.name. And this is where I get stuck.

    This works fine
    Code:
    SELECT A.*, D.name
    FROM A, D
    WHERE A.division = D.id
    AND A.division LIKE '%term%'
    This works fine too
    Code:
    SELECT A.*, D.name
    FROM A, D
    WHERE A.division = D.id
    AND D.name LIKE '%term%'
    This yields all results like 11 times, sets D.name to the search term and generally makes no sense
    Code:
    SELECT A.*, D.name
    FROM A, D
    WHERE A.division = D.id
    AND D.name LIKE '%term%'
    OR A.description = '%term%'
    I've tried JOINS too, but that failed miserably (probably due to the lack of experience on the JOIN front). Looked into full text search but couldn't see the use of that.

    So, if anyone can give me a heads up on the query structure, that would be great. Oh, and keep in mind that A has got a few more columns that need searching with 1 query too...
    Last edited by Thyrosis; 01-08-2013 at 08:45 AM. Reason: broken sentence

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Time for a lesson in basic arithmetic.

    What is the value of
    Code:
        
        3 + 2 * 5
    ???

    If you said 15, go back to elementary school.

    Multiplication has a higher PRECEDENCE than addition. So the correct answer is 13.

    If you *wanted* an answer of 15 then you would need to write
    Code:
        ( 3 + 2 ) * 5
    **************

    Guess what? SAME THING applies for BOOLEAN operators AND and OR!!!

    AND has higher precedence.

    So your query is, effectively, doing:
    Code:
    SELECT A.*, D.name
    FROM A, D
    WHERE ( A.division = D.id AND D.name LIKE '%term%' )
    OR A.description LIKE '%term%'
    (You wrote A.description = '%term%' but I'm sure that was a typo...I'm sure you meant both tests to use LIKE.)

    In other words, if you get a match on A.description, your *other* part of the WHERE clause is being *IGNORED*!

    Easy fix. Just as with arithmetic:
    Code:
    SELECT A.*, D.name
    FROM A, D
    WHERE A.division = D.id
      AND ( D.name LIKE '%term%' OR A.description LIKE '%term%' )
    You can also fix it by using
    Code:
    SELECT A.*, D.name
    FROM A INNER JOIN D ON A.division = D.id
    WHERE D.name LIKE '%term%'
       OR A.description LIKE '%term%'
    But that's kind of a cheat. You really need to remember to use parentheses with AND and OR when both are involved.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Thyrosis (01-08-2013)

  • #3
    Regular Coder
    Join Date
    Nov 2012
    Posts
    115
    Thanks
    7
    Thanked 12 Times in 12 Posts
    Damn, I knew I should've paid attention in math class...

    Can't believe I didn't think of that though! Thanks for pointing it out!

    (Oh, and I think you should go back to elementary school too, because in my class I was tought that (3 + 2) * 5 is actually 25. But that's not the point =D )

    Thanks again!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    LOL!

    DOH DOH DOH on me!

    Teach me not to actually read what I'm writing.

    But in my case I have an excuse. Senility is starting to set in.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Nov 2012
    Posts
    115
    Thanks
    7
    Thanked 12 Times in 12 Posts
    Haha, yeah blame it on the Senility again lol

    Thanks again though, I've modified it a bit and works like a charm.


  •  

    Posting Permissions

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