Duplicate Results

Discussion in 'Databases' started by Weirfire, Jun 16, 2007.

0
  1. #1
    Can anyone explain why this query :

    
SELECT p.id, p.prop_thumb_main, p.prop_address, ps.property_status FROM properties p, properties_status ps WHERE (prop_address LIKE '%Hol%' OR prop_address2 LIKE '%Hol%' OR prop_postcode LIKE '%Hol%') AND ps.property_id = p.id
    Code (markup):
    Would give only 1 result (Correctly) and this query :

    
SELECT p.id, p.prop_thumb_main, p.prop_address, ps.property_status FROM properties p, properties_status ps WHERE prop_address LIKE '%Hol%' OR prop_address2 LIKE '%Hol%' OR prop_postcode LIKE '%Hol%' AND ps.property_id = p.id
    Code (markup):
    gives 2 duplicate results? (incorrectly)


    The only difference is the parenthesis around the patterning matching queries.
     
    Weirfire, Jun 16, 2007 IP
  2. Clark Kent

    Clark Kent Guest

    Messages:
    122
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #2
    You gave the answer yourself :)
    Result is different because of paranthesis.
    In the second query "AND ps.property_id = p.id" this becomes useless because the other parts uses "or". If one equation matches then whole query returns true because you are using "OR"

    Just like mathemetic.
     
    Clark Kent, Jun 16, 2007 IP