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 4 of 4
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Query Returning Wrong Results

    Hello guys,

    Can some one shed some light on why the below query is returning the wrong results. :'( :'(

    Code:
    SELECT campaign_details.pid,campaign_summary.uid,campaign_summary.customer_id FROM campaign_summary,campaign_details WHERE campaign_summary.camp_id=campaign_details.camp_id AND campaign_summary.uid='1' OR campaign_summary.uid='11' AND campaign_summary.customer_id='205' AND campaign_details.pid='7' GROUP BY campaign_details.pid
    I am explicitly telling stupid sql to return only where the campaign_details.pid matches with 7 and campaign_summary.customer_id matches with 205.
    But I get the below result set.
    pid----uid---customer_id
    1------1-----65
    2------1-----84
    4------1-----81
    5------1-----63
    7------1-----65
    13-----1-----63

    Why is it returning PID's other than 7?
    Why is it returning customer_id's other than 205?
    There should be no results at all.

    Can someone please shed some light?
    Mysql server v5.0.51

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,569
    Thanks
    23
    Thanked 643 Times in 642 Posts
    You need to put () parentheses around your WHERE statements to group them into
    what you want from your query.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,054
    Thanks
    80
    Thanked 4,550 Times in 4,514 Posts
    In other words:
    Code:
    SELECT D.pid, S.uid, S.customer_id 
    FROM campaign_summary AS S, campaign_details AS D 
    WHERE S.camp_id=D.camp_id 
    AND ( S.uid='1' OR S.uid='11' )
    AND S.customer_id='205' 
    AND D.pid='7' 
    GROUP BY D.pid
    Why do you put '...' around *numbers*????

    And you can eliminate the need for the OR if you learn to use IN.

    So this is better:
    Code:
    SELECT D.pid, S.uid, S.customer_id 
    FROM campaign_summary AS S, campaign_details AS D 
    WHERE S.camp_id=D.camp_id 
    AND S.uid IN (1, 11)
    AND S.customer_id = 205
    AND D.pid = 7 
    GROUP BY D.pid
    Not sure why you think you need the GROUP BY, either, but it won't hurt.

    Probably an ORDER BY would be more useful.
    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.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,054
    Thanks
    80
    Thanked 4,550 Times in 4,514 Posts
    By the by, the reason it didn't work as written is because AND has higher precedence than OR.

    So you were *effectively* asking for this:
    Code:
    SELECT D.pid, S.uid, S.customer_id 
    FROM campaign_summary AS S, campaign_details AS D 
    WHERE ( S.camp_id=D.camp_id AND S.uid='1' )
    OR ( S.uid='11' AND S.customer_id='205' AND D.pid='7' )
    GROUP BY D.pid
    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.


  •  

    Posting Permissions

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