CODEWITHSUNDEEP
mysql
Rein
Rein

Reputation: 1368

Use different WHERE-clause based on CASE outcome in SQL

I did find some answers to similar questions, but can't quite figure it out yet.

I have a MySQL table with tyres in it. A tyre record consists of the columns:

  1. brand
  2. width
  3. height
  4. diameter
  5. price

When the width, height and diameter match certain values, I need to select tyres of certain brands. If it doesn't, I need to select tyres of other, partially overlapping, brands.

What I need to achieve, in bogus-SQL:

SELECT * FROM tyres
IF (width = 275 AND height = 55 AND diameter = 18)
    OR (width = 270 AND height = 55 AND diameter = 17)
    OR (width = 290 AND height = 45 AND diameter = 19)
THEN WHERE_CLAUSE = "WHERE brand = 'dunlop' OR brand = 'michelin' OR brand = 'vredestein'"
ELSE WHERE_CLAUSE = "WHERE brand = 'goodyear' OR brand = 'michelin' OR brand = 'ferrari'"

I've tried many notations like below, but (obviously) keep getting syntax error #1064:

SELECT * FROM tyres
CASE WHEN width = '275' AND height = '55' AND diameter = '18' THEN " WHERE brand = 'dunlop' OR brand = 'michelin' OR brand = 'vredestein'"
ELSE " WHERE brand = 'goodyear' OR brand = 'michelin' OR brand = 'ferrari'"

How can I use a specific WHERE clause, based on the outcome of the CASE statement?

Upvotes: 1

Views: 102

Answers (1)

Edper
Edper

Reputation: 9322

Try UNION ALL:

SELECT * FROM tyres
WHERE width = 275 AND height = 55 AND diameter = 18
AND (brand = 'dunlop' OR brand = 'michelin' OR brand = 'vredestein')
UNION ALL
SELECT * FROM tyres
WHERE width <> 275 OR height <> 55 OR diameter <> 18
AND (brand = 'goodyear' OR brand = 'michelin' OR brand = 'ferrari')

The first SELECT is the THEN portion in your post and the second SELECT is the ELSE portion.

Upvotes: 1

Related Questions