CODEWITHSUNDEEP
mysqlsqlcriteriawhere-clause
J Young
J Young

Reputation: 755

SQL query not returning the criteria set

I am developing a flowerbed planting system, and have written an SQL statement which queries 8 tables in order to return a list of suitable plants.

Table structure: http://pastebin.com/0DUYQis1 [outputted from phpmyadmin]

Example Data: http://gyazo.com/13dad0b3370b5053dbcad2a32013a54a / http://gyazo.com/1bd313adb3e8ccd01354c979e69fd059

SQL Statement:

SELECT Plant.Plant_ID, Plant.Plant_Quantity, Plant.Plant_Price, Plant.Plant_Description, Plant.Plant_Latin_Name, Plant.Plant_Common_Name, Plant.Plant_Height, Plant.Plant_Spread, Plant.Plant_Type, Plant.Plant_Family, Plant.Plant_Picture, Plant_Aspect.Plant_Aspect, Plant_Flower_Colour.Plant_Flower_Colour, Plant_Flower_Colour.Plant_Season, Plant_Foliage_Colour.Plant_Foliage_Colour, Plant_Foliage_Colour.Plant_Season, Plant_Moisture.Plant_Moisture, Plant_Soil_PH.Plant_Soil_PH, Plant_Soil_Type.Plant_Soil_Type, Plant_Sun_Type.Plant_Sun_Type 

FROM Plant 

INNER JOIN Plant_Aspect ON Plant.Plant_ID = Plant_Aspect.Plant_ID 
INNER JOIN Plant_Flower_Colour ON Plant.Plant_ID = Plant_Flower_Colour.Plant_ID 
INNER JOIN Plant_Foliage_Colour ON Plant.Plant_ID = Plant_Foliage_Colour.Plant_ID 
INNER JOIN Plant_Moisture ON Plant.Plant_ID = Plant_Moisture.Plant_ID
INNER JOIN Plant_Soil_PH ON Plant.Plant_ID = Plant_Soil_PH.Plant_ID 
INNER JOIN Plant_Soil_Type ON Plant.Plant_ID = Plant_Soil_Type.Plant_ID 
INNER JOIN Plant_Sun_Type ON Plant.Plant_ID = Plant_Sun_Type.Plant_ID 

WHERE Plant_Aspect.Plant_Aspect = 'East-facing'
OR Plant_Aspect.Plant_Aspect = 'Any'
AND Plant_Soil_Type.Plant_Soil_Type = 'Sand' 
OR Plant_Soil_Type.Plant_Soil_Type = 'Any'
AND Plant_Moisture.Plant_Moisture = 'Well-drained' 
OR Plant_Moisture.Plant_Moisture = 'Any'
AND Plant_Soil_PH.Plant_Soil_PH = 'Acid'
OR Plant_Soil_PH.Plant_Soil_PH = 'Any'
AND Plant_Foliage_Colour.Plant_Foliage_Colour = 'Green'
AND Plant_Foliage_Colour.Plant_Season = 'Winter'
OR Plant_Foliage_Colour.Plant_Season = 'Any'
AND Plant_Flower_Colour.Plant_Flower_Colour = 'Orange'
AND Plant_Flower_Colour.Plant_Season = 'Winter'
OR Plant_Flower_Colour.Plant_Season = 'Any'

GROUP BY Plant_ID;

I was expecting the query to return only those plants that have a record matching the search criteria, ie. only those plants that can be planted in the season 'Winter' or 'Any', but instead it is returning plants it shouldn't be.

For example: http://gyazo.com/d2ca989fc6def79854a1d9b11e4bc310 - Plant_ID: 1 contains only one record in the Plant_Flower_Colour table - Dark Blue during Spring. Why is it this record has been returned? It's as if the trailing ANDs and ORs are being ignored.

Any help would be much appreciated!

Upvotes: 1

Views: 127

Answers (5)

wildplasser
wildplasser

Reputation: 44250

An alternative form for the and/or would be to use IN() clauses, which is easyer to read, easyer to edit and less accident-prone.

WHERE Plant_Aspect.Plant_Aspect IN( 'East-facing' , 'Any' )
AND Plant_Soil_Type.Plant_Soil_Type IN( 'Sand' , 'Any' )
AND Plant_Moisture.Plant_Moisture IN( 'Well-drained' , 'Any' )
AND Plant_Soil_PH.Plant_Soil_PH IN( 'Acid' , 'Any' )
AND Plant_Foliage_Colour.Plant_Foliage_Colour IN( 'Green' )
AND Plant_Foliage_Colour.Plant_Season IN( 'Winter' , 'Any' )
AND Plant_Flower_Colour.Plant_Flower_Colour IN( 'Orange' )
AND Plant_Flower_Colour.Plant_Season IN( 'Winter' , 'Any' )

Upvotes: 3

Jonathan Leffler
Jonathan Leffler

Reputation: 754500

The grouping in your WHERE clause is wrong — and ambiguous to the average reader. AND binds tighter than OR, so you need to use parentheses to make your intention clear to the DBMS:

WHERE (Plant_Aspect.Plant_Aspect = 'East-facing'
   OR  Plant_Aspect.Plant_Aspect = 'Any')
  AND (Plant_Soil_Type.Plant_Soil_Type = 'Sand' 
   OR  Plant_Soil_Type.Plant_Soil_Type = 'Any')
  AND (Plant_Moisture.Plant_Moisture = 'Well-drained' 
   OR  Plant_Moisture.Plant_Moisture = 'Any')
  AND (Plant_Soil_PH.Plant_Soil_PH = 'Acid'
   OR  Plant_Soil_PH.Plant_Soil_PH = 'Any')
  AND  Plant_Foliage_Colour.Plant_Foliage_Colour = 'Green'
  AND (Plant_Foliage_Colour.Plant_Season = 'Winter'
   OR  Plant_Foliage_Colour.Plant_Season = 'Any')
  AND  Plant_Flower_Colour.Plant_Flower_Colour = 'Orange'
  AND (Plant_Flower_Colour.Plant_Season = 'Winter'
   OR  Plant_Flower_Colour.Plant_Season = 'Any')

What you wrote was treated as if you had written:

WHERE  Plant_Aspect.Plant_Aspect = 'East-facing'
   OR (Plant_Aspect.Plant_Aspect = 'Any'
  AND  Plant_Soil_Type.Plant_Soil_Type = 'Sand')
   OR (Plant_Soil_Type.Plant_Soil_Type = 'Any'
  AND  Plant_Moisture.Plant_Moisture = 'Well-drained')
   OR (Plant_Moisture.Plant_Moisture = 'Any'
  AND  Plant_Soil_PH.Plant_Soil_PH = 'Acid')
   OR (Plant_Soil_PH.Plant_Soil_PH = 'Any'
  AND  Plant_Foliage_Colour.Plant_Foliage_Colour = 'Green'
  AND  Plant_Foliage_Colour.Plant_Season = 'Winter')
   OR (Plant_Foliage_Colour.Plant_Season = 'Any'
  AND  Plant_Flower_Colour.Plant_Flower_Colour = 'Orange'
  AND  Plant_Flower_Colour.Plant_Season = 'Winter')
   OR  Plant_Flower_Colour.Plant_Season = 'Any'

Upvotes: 2

Mayur Manani
Mayur Manani

Reputation: 825

You need to use parenthesis to group your where criteria.

WHERE 
(Plant_Aspect.Plant_Aspect = 'East-facing'
OR Plant_Aspect.Plant_Aspect = 'Any')
AND (Plant_Soil_Type.Plant_Soil_Type = 'Sand' 
OR Plant_Soil_Type.Plant_Soil_Type = 'Any')...

Upvotes: 3

Victor
Victor

Reputation: 666

Try placing brackets around the OR comparisons.

WHERE (Plant_Aspect.Plant_Aspect = 'East-facing'
OR Plant_Aspect.Plant_Aspect = 'Any')
AND (Plant_Soil_Type.Plant_Soil_Type = 'Sand' 
OR Plant_Soil_Type.Plant_Soil_Type = 'Any')
AND (Plant_Moisture.Plant_Moisture = 'Well-drained' 
OR Plant_Moisture.Plant_Moisture = 'Any')
AND (Plant_Soil_PH.Plant_Soil_PH = 'Acid'
OR Plant_Soil_PH.Plant_Soil_PH = 'Any')
AND Plant_Foliage_Colour.Plant_Foliage_Colour = 'Green'
AND (Plant_Foliage_Colour.Plant_Season = 'Winter'
OR Plant_Foliage_Colour.Plant_Season = 'Any')
AND Plant_Flower_Colour.Plant_Flower_Colour = 'Orange'
AND (Plant_Flower_Colour.Plant_Season = 'Winter' OR Plant_Flower_Colour.Plant_Season = 'Any')

Upvotes: 2

sgeddes
sgeddes

Reputation: 62851

I think at minimum you need to include parentheses around your OR criteria:

...
WHERE (Plant_Aspect.Plant_Aspect = 'East-facing'
OR Plant_Aspect.Plant_Aspect = 'Any')
AND (Plant_Soil_Type.Plant_Soil_Type = 'Sand' 
OR Plant_Soil_Type.Plant_Soil_Type = 'Any')
...

Upvotes: 3

Related Questions