SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Nov 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is the correct way to do this query?

    I have a fairly simple query:

    Code:
    SELECT * FROM table WHERE name = 'Horseracing' AND DESCRIPTION LIKE '%tips%' OR TITLE LIKE '%tips%' LIMIT 0, 25
    The name field is my category field. This query returns results across all categories (name field). What I need is all records with the category of horseracing and then find ones with keys in the title OR description.

    Can you help?

    Thanks, great site.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    this is your query (formatted for readability) --
    Code:
    SELECT * 
      FROM table 
     WHERE name = 'Horseracing' 
       AND DESCRIPTION LIKE '%tips%' 
        OR TITLE LIKE '%tips%'
    the problem you are having is the precedence of ANDs over ORs

    your query is actually evaluated as though it had parentheses like this --
    Code:
    SELECT * 
      FROM table 
     WHERE (
           name = 'Horseracing' 
       AND DESCRIPTION LIKE '%tips%' 
           ) 
        OR TITLE LIKE '%tips%'
    what you probably wanted was the following --
    Code:
    SELECT * 
      FROM table 
     WHERE name = 'Horseracing' 
       AND (
           DESCRIPTION LIKE '%tips%' 
        OR TITLE LIKE '%tips%' 
           )
    see the difference?

    when mixing ANDs and ORs, always code your own parentheses to control the evaluation sequence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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