CODEWITHSUNDEEP
phpmysql
Elitmiar
Elitmiar

Reputation: 36829

mySql returning incorrect results

I have a query 

SELECT date, SUM(cimpression) AS cimp, SUM(cclick) AS cclick 
FROM stdaily 
WHERE date BETWEEN :start AND :end 
AND (id = 21 AND id = 32 AND id = 12 AND id = 33)
GROUP BY date
ORDER BY date ASC;

This query needs to return only results where the id is equal to 21,32,12 and 33 but returning results not containing those specific ID's , not sure what exactly is wrong? Any advise?

Upvotes: 2

Views: 260

Answers (5)

UltraCommit
UltraCommit

Reputation: 2276

Your query is not correct because ID cannot be in the same time all these values:

id = 21 AND id = 32 AND id = 12 AND id = 33

The previous conditions fail, and the result is NULL.

Try this:

id = 21 OR id = 32 OR id = 12 OR id = 33

Upvotes: 1

method
method

Reputation: 782

I don't know how your data is structured but I'm surprised this returns any results. How can the id be both 21 AND 32? For example, if I use

SELECT * FROM users WHERE 1=1 AND (id = 10 AND id = 11);

I won't get a result even if there are users with ids 10 and 11.

You should use OR or better, IN(21,32,12,33)

Upvotes: 1

Christophe
Christophe

Reputation: 4828

you are not specifying where he should check for those id's, and the way you pass the id is wrong as well

WHERE date BETWEEN :start AND :end AND (id = 21 AND id = 32 AND id = 12 AND id = 33 )

It should be something like:

SELECT date,SUM(cimpression) AS cimp,SUM(cclick) AS cclick 
FROM stdaily 
WHERE date BETWEEN :start AND :end AND id IN (21, 32, 12, 33 ) 
GROUP BY date 
ORDER BY date ASC

Upvotes: 1

BugFinder
BugFinder

Reputation: 17858

id = 21 AND id = 32 AND id = 12 AND id = 33

should be

id = 21 OR id = 32 OR id = 12 OR id = 33

Upvotes: 1

Tim
Tim

Reputation: 5822

What I think you are looking for is: 

SELECT date,SUM(cimpression) AS cimp, SUM(cclick) AS cclick
FROM stdaily
WHERE date BETWEEN :start AND :end 
AND id in (21, 32, 12, 33) 
GROUP BY date 
ORDER BY date ASC;

This will return results, which have an id as any of those contained within the in brackets.

Upvotes: 3

Related Questions