CODEWITHSUNDEEP
mysql
Mario Boulanger
Mario Boulanger

Reputation: 1

Using if() in a where clause

I need to do an if else on a where clause.

I want to do something like

Select id from table where if(client_id = 10, client_id = 10, client_id = 1) AND foo = A

In clear I need if client_id 10 exist where foo = A return id else client_id 10 doesn't exist where foo = A use client_id = 1 where foo = A

Upvotes: 0

Views: 77

Answers (1)

FanoFN
FanoFN

Reputation: 7114

I don't think you need an IF. You could try with this query:

SELECT id FROM table WHERE client_id in (10,1) AND foo='A';

EDIT:

Query example 1: 

SELECT IF(client_id=10 AND foo='A',id,'') AS '10A',
IF(client_id <> 10 AND client_id=1 AND foo='A',id,'') AS '1A'
FROM table HAVING (10A OR 1A) <> '';

Query example 2:

SELECT id
FROM table 
WHERE 
client_id=CASE WHEN client_id=10 AND foo='A' THEN client_id
WHEN client_id <> 10 AND client_id=1 AND foo='A' THEN client_id  
END;

Query example 3:

SELECT id
FROM table 
WHERE 
client_id=IF(client_id=10 AND foo='A',client_id,
IF(client_id <> 10 AND client_id=1 AND foo='A',client_id,''));

The last example could be what you initially have in mind.

Upvotes: 1

Related Questions