CODEWITHSUNDEEP
sqloracle-database
Olaf
Olaf

Reputation: 95

If statement in clause where

I need add a where clause only if a condition is respected, for example : 

SELECT * FROM car Where <a condition> 
and ( if (car.brand = 'Renault') then car.color = 'red')

In my example : if the brand is renault i want only red cars and I want other car only if there isn't renault

Is it possible to write something like this in SQL (without PLSQL)?

Upvotes: 0

Views: 83

Answers (3)

Mark Rotteveel
Mark Rotteveel

Reputation: 109242

You can't use an if in a where clause (SQL doesn't even know if). To achieve what you want you need to use something like:

where ... and (car.brand = 'Renault' and car.color = 'red' or car.brand <> 'Renault')

This disjunction says "if the brand is Renault, then the color needs to be red, for other brand it doesn't matter".

If you - as you comment - " want only renault red, ... other car only if there isn't renault", then you need check for non-existence of renault:

where ... 
and (car.brand = 'Renault' and car.color = 'red' 
     or not exists (select * from car where car.brand = 'Renault'))

Upvotes: 2

Ilia Maskov
Ilia Maskov

Reputation: 1898

Try this 

SELECT * FROM car Where <a condition> 
and (car.brand = 'Renault' AND car.color = 'red' OR car.brand <> 'Renault')

Upvotes: 0

StoicFnord
StoicFnord

Reputation: 193

Simple logic below.

You might want to write a truth table for any query like this if you are not sure.

SELECT * 
FROM car 
WHERE <a condition> 
AND ((car.brand= 'Renault' and car.color = 'red') OR car.brand != 'Renault')

Upvotes: 1

Related Questions