...

View Full Version : Query Returning Wrong Results



elabuwa
11-17-2011, 02:02 AM
Hello guys,

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



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

sunfighter
11-17-2011, 04:17 AM
You need to put () parentheses around your WHERE statements to group them into
what you want from your query.

Old Pedant
11-17-2011, 05:08 AM
In other words:


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:


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.

Old Pedant
11-17-2011, 05:10 AM
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:


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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum