CODEWITHSUNDEEP
mysqlsqlwhere-clause
redson
redson

Reputation: 13

WHERE condition isn't respected

currently I'm working with the AdventureWorks database and playing around with some queries. I've tried to solve the following exercise:

Order all products by its ProductID, which

This is my query:

SELECT pp.ProductID, pp.Name, pps.Name FROM Production.Product pp

JOIN Production.ProductCostHistory ppch
ON pp.ProductID = ppch.ProductID

JOIN Production.ProductSubcategory pps
ON pps.ProductSubcategoryID = pp.ProductSubcategoryID

WHERE pps.Name LIKE 'Helmets' OR pps.Name LIKE 'Gloves'
    AND pp.Color LIKE 'Yellow' OR pp.Color LIKE 'Blue' OR pp.Color LIKE 'Black'

GROUP BY pp.ProductID, pp.Name, pps.Name
HAVING Count(ppch.ModifiedDate) > 1
ORDER BY pp.Name ASC

This is my result:

enter image description here

Now, to pose my question. Allthough I connected two conditions with 'AND' I receive results of two other categories, which were listed further to the matching colors ('Road Frames' & 'Road Bikes') and I don't quiet understand why.

Shouldn't the result only show matches which fullfil both conditions?

I'm sure that there are probably much better ways to achieve this, but I'm fairly new to SQL and trying to get the basics.

I would really appreciate some help

Upvotes: 1

Views: 75

Answers (1)

Christoph
Christoph

Reputation: 4401

Change your WHERE clause to explicitly indicate groups of conditions instead of allow the precedence of ANDs and ORs determine the result.

WHERE
(pps.Name LIKE 'Helmets' OR pps.Name LIKE 'Gloves')
    AND
    (pp.Color LIKE 'Yellow' OR pp.Color LIKE 'Blue' OR pp.Color LIKE 'Black')

or better

WHERE pps.Name IN('Helmets','Gloves')
    AND
    pp.Color IN ('Yellow','Blue','Black')

Upvotes: 3

Related Questions