Solved

problem with mysql statement OR

Posted on 2009-07-03
5
189 Views
Last Modified: 2013-12-13
Hi there,

for some reason when i use this mysql statement:

SELECT COUNT(*) FROM projects JOIN link_projects_zones ON projects.proj_ID = link_projects_zones.proj_ID WHERE link_projects_zones.zone_ID = 1 AND projects.proj_Status = 2 or 3 or 4

It returns the entire number of records in the projects table rather than just the records where proj_Status = 2,3 or 4. I am having the same problem when querying "2 or 3" and basically anything with OR in it. Any ideas?

0
Comment
Question by:cookiejest
5 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 24773603
AND (projects.proj_Status = 2 or  projects.proj_Status = 3 or  projects.proj_Status  = 4 )
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 24773607
AND projects.proj_Status IN ( 2, 3 , 4 )

OR

AND projects.proj_Status BETWEEN 2 AND 4
0
 
LVL 5

Accepted Solution

by:
Opalelement earned 250 total points
ID: 24773638
Just to help him out for next time, I am going to tell him what was wrong.

The query:

SELECT COUNT(*) FROM projects JOIN link_projects_zones ON projects.proj_ID = link_projects_zones.proj_ID WHERE link_projects_zones.zone_ID = 1 AND projects.proj_Status = 2 or 3 or 4

That "or 3 or 4" is evaluating each expression individually. A single positive number will always be true, so since 3 and 4 are considered to be individual expressions, they are both ture. Since you said "or", it only needs one true to select it all.

To fix yours, simply add the "projects.proj_Status = " before the 3 and the 4, like so:
SELECT COUNT(*) FROM projects JOIN link_projects_zones ON projects.proj_ID = link_projects_zones.proj_ID WHERE link_projects_zones.zone_ID = 1 AND projects.proj_Status = 2 or projects.proj_Status = 3 or projects.proj_Status = 4
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24773712
When you combine AND and OR in the same expression, it is important to use parantheses to make it clear what is meant. Note the difference between these expressions:

link_projects_zones.zone_ID = 1 AND (projects.proj_Status = 2 or projects.proj_Status = 3 or projects.proj_Status = 4)
(link_projects_zones.zone_ID = 1 AND projects.proj_Status = 2) or projects.proj_Status = 3 or projects.proj_Status = 4

The last expression will ignore the zone_ID=1 requirement if proj_Status is 3 or 4. Without the parantheses, this is how MySQL interprets it.
0
 

Author Closing Comment

by:cookiejest
ID: 31599629
thanks for all the answers!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL question - need unique values for one column that is not displayed 2 22
SYbase 4 31
mysql date time 14 31
Need help constructing a conditional update query 16 47
This article discusses how to create an extensible mechanism for linked drop downs.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question