0

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

Mario Boulanger
  • 1
  • 2

1 Answers1

1

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.

FanoFN
  • 6,815
  • 2
  • 13
  • 33
  • Yes i need it because if 10 exist i don't want 1. – Mario Boulanger Apr 04 '19 at 14:40
  • You could use this answer with slight modification; just add `ORDER BY client_id DESC LIMIT 1` to the end of it. – Uueerdo Apr 04 '19 at 16:48
  • If like that, there are a few ways to do it. The two suggestion from @Uueerdo (using `IF` in `SELECT` and the suggestion above) can work. It depends on which is feasible for your needs. I'll try to add more query suggestion and you can test each one to see which will be most useful for you. – FanoFN Apr 05 '19 at 00:21
  • 1
    @tcadidot0 You probably should have just stuck with your original suggestion and added the order by I mentioned; the others the edit added are so convoluted if I saw them in code I'd think someone was trying to obfuscate their query for no good reason... and they'd still need that `order by` added. – Uueerdo Apr 05 '19 at 16:32
  • Wow thank you. that's perfect. Exactly what needed. Since I was in a rush I did it in 2 query but I will correct it later on. – Mario Boulanger Apr 05 '19 at 18:04
  • You're right @Uueerdo . I wasn't thinking into how these queries will perform in a program code. I was just making queries as how I would do if I retrieve directly from db instead. – FanoFN Apr 06 '19 at 02:45