I am using the following query to get data from mysql database and I get wrong data. I want to get all data with the cart_Status of 2 or 3 which have the view_Status of 1:
SELECT * FROM `cart` WHERE `view_Status` = 1 AND cart_Status = 2 OR `cart_Status` = 3
This is how my data structure and table looks like:
But in result, it returns something regardless of view_Status = 1 which is not my target.
it returns :
Of course, it should not return anything! But, it does!
This is about operator precendence.
Your query evaluates as
SELECT * FROM `cart` WHERE (`view_Status` = 1 AND cart_Status = 2) OR `cart_Status` = 3
You should to add parentheses:
SELECT * FROM `cart` WHERE `view_Status` = 1 AND (cart_Status = 2 OR `cart_Status` = 3)
SELECT * FROM `cart` WHERE `view_Status` = 1 AND (cart_Status = 2 OR `cart_Status` = 3)
or better
SELECT * FROM `cart` WHERE `view_Status` = 1 AND cart_Status in (2, 3);
You appear to be learning SQL. Use parentheses in the WHERE clause, particularly when you mix AND and OR.
However, in your case, IN is a better solution:
SELECT c.*
FROM `cart` c
WHERE c.view_Status = 1 AND cart_Status IN (2, 3);
It's a problem with operators precedence. Typically AND is executed before OR in programming languages (think of AND as of multiplication of bits, and of OR as of addition of bits and precedence becomes familiar). So, your condition:
`view_Status` = 1 AND cart_Status = 2 OR `cart_Status` = 3
is parsed like this:
(`view_Status` = 1 AND cart_Status = 2) OR `cart_Status` = 3
which results in all rows with specific cart_Status to be selected. You have to add parenthesis around the second clause:
`view_Status` = 1 AND (cart_Status = 2 OR `cart_Status` = 3)
or, even shorter:
`view_Status` = 1 AND cart_Status IN (2, 3)
SELECT * FROMcart` WHEREview_Status= 1 AND (cart_Status = 2 ORcart_Status= 3)`