PHP mysql subqueries filtering

Question

I have a table like this:

id     price    condition     username
----------------------------------------
 1       20     New           Kim 
 2       30     Used          Kim
 3       40     Used          George
 4       60     New           Tom

My question is about subselection. when I use this query,

SELECT *
FROM `table`
WHERE `username` = 'kim'
  AND `price` = '20'
   OR `condition` = 'New'

it becomes true for the last condition and brings 2 records where condition = 'New'

However, when I use AND, it returns the correct record.

SELECT *
FROM `table`
WHERE `username` = 'kim'
  AND `price` = '20'
  AND `condition` = 'New'

I want to nest the query such that I can select price and condition only from the results of the first query, which is username. Thanks

Rafał Wójcik · Accepted Answer · 2012-04-11 15:28:09Z

3

SELECT * FROM table WHERE username = 'kim' AND (price = '20' OR condition = 'New');

answered Apr 11, 2012 at 15:28

Rafał Wójcik

5763 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikhail · Accepted Answer · 2012-04-11 15:32:47Z

2

As Rafal Wojcik said you need to utilize some parenthesis to tell the database what OR you're grouping.

Without any guidence the engine groups all your conditionals as they come:

username = 'kim' AND price = 20 OR condition = 'New'

reads as

((username = 'kim' AND price = 20) OR condition = 'New')

As you see this is not what you wanted. Here OR applies to "kim-20" OR "New"

Same issue is observed with your all AND case.

Good luck!

answered Apr 11, 2012 at 15:32

Mikhail

9,0569 gold badges58 silver badges89 bronze badges

Collectives™ on Stack Overflow

PHP mysql subqueries filtering

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related