Why is this SQL statement incorrect?

Asked

Viewed 114 times

9

SELECT * FROM produtos WHERE categoria = 'Vestidos' AND cor = 'Branco' OR cor = 'Rosa'

Returned 3 records, 2 of them from the category Dresses, and 1 of the category Shoes. Why not returned only from the category Dresses since I have instructed categoria = 'Vestidos'?

sql

  • You can place the full content of your table in the body of the question?

  • The shoe by chance is color = pink, check if the result was not white dress or anything pink?

  • yes, too! all the records match the color, but the category does not(in case pulled Shoes instead of Dresses as I asked..)

  • Do you think the OR there is as if, I ask the SQL dressed in white or Pink color(there could be pink shoe)..

4 answers

13

The AND takes precedence over the OR. So your criterion is understood like this:

(categoria = 'Vestidos' AND cor = 'Branco') OR cor = 'Rosa'

You need to put the parentheses in the right place to achieve the desired result.

  • obg for the help!

6

Alternatively the other answers, you can also use the comparison operator IN to check if the desired value is among a set of values, example:

SELECT * 
FROM produtos 
WHERE categoria = 'Vestido' AND cor IN ('branco','rosa');

5

SELECT * FROM produtos WHERE categoria = 'Vestidos' AND (cor = 'Branco' OR cor = 'Rosa')

  • obg by suggestion!

5

Understand your query as the following (explaining the bar conversation mode): You want to know if there are products of the category 'Dresses' and the color 'White' and if it is not that (OR) that is the color 'Pink', that is, it forgets the two previous conditions and starts to interpret only the latter, or if there are results for the two conditions it returns.

So if the condition color = 'Pink' is part of the condition category = 'Dresses', your query should associate the "OR’s" with parentheses, being:

SELECT * FROM produtos WHERE categoria = 'Vestidos' AND (cor = 'Branco' OR cor = 'Rosa')

The right shape

  • 4

    Oh perfect.. my insconciente was already interpreting this way, but forgot that the code also needs to know what I want, kkk! thanks!!

  • Your wife says, "Love, go to the supermarket and buy six eggs. If you have tomatoes, buy nine." You bring what?

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.