7
I’m having a doubt in a query mysql.
I have this query
select cor from cores where cor in('verde','vermelho');
The problem is that I want the green, and If there is no green I want the red. but not the 2 results.
I can’t use the LIMIT 1, because this is inside a subquery more complex and Mysql won’t let you use LIMIT in subquery.
7
Instead of the operator IN, you can use the CASE to check if a record exists. It’s like making a IF in a structured language.
Take an example:
SELECT
CASE
WHEN EXISTS(select cor from cores where cor = 'verde') THEN 'verde'
ELSE (select cor from cores where cor = 'vermelho')
END,
CASE
WHEN EXISTS(select cor from cores where cor = 'azul') THEN 'azul'
WHEN EXISTS(select cor from cores where cor = 'verde') THEN 'verde'
ELSE (select cor from cores where cor = 'vermelho')
END
Alternatively, you can use the function COALESCE, which will return the first parameter that is not null, thus establishing a priority order.
Take the example:
select
COALESCE(
(select cor from cores where cor = 'azul'),
(select cor from cores where cor = 'verde'),
(select cor from cores where cor = 'vermelho')
)
See also the sqlfiddle with the functional examples.
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
I didn’t know about this Sqlfiddle, very helpful, thank you :)
– Paulo Roberto Rosa
I was in doubt in the example of
CASE, he will execute the twoCASEs or he tries to execute one and, if nothing comes back, he executes the other?– Felipe Avelar
@That’s right, it should work like a
if/else: if there is 'blue', then return 'blue, otherwise select 'red'.– utluiz
@Felipeavelar But note that you have several different 'WHEN'. I will edit the answer to illustrate this.
– utluiz
Worth the query I was using was a
inner join onand with the coalesce was perfect. I used it as follows:on coalesce((RIGHT(upper(c.filename), CHAR_LENGTH(b.value) + 4) = concat(b.value, '.PSD')), (RIGHT(upper(c.filename), CHAR_LENGTH(b.value) + 4) = concat(b.value, '.JPG')))– ricardo
I understood the
WHEN, what I didn’t understand was theCASE. Like, there’s twoCASEs in theSELECT, but the query will always run bothCASEs or only when one has no return? Understood what I wanted to ask?– Felipe Avelar
@Felipeavelar I think which I now understand. In the example I put select will run both cases, would be as two separate and independent columns.
– utluiz
That’s exactly what it was, thank you.
– Felipe Avelar