Is it possible to use if else in MySQL queries?
I have been searching the internet and hearing rumors that it is possible to use if else in MySQL queries but I have not seen anything concrete about it. Is it really possible? If so, how?
I want to make a query in three distinct tables in search of the name column I have the following query as an example
SELECT `PAIS`.nome, `FILHOS`.nome, `NETOS`.nome FROM `PAIS`, `FILHOS`, `NETOS` WHERE `PAIS`.nome LIKE '%MATILDE%' OR `FILHOS`.nome LIKE '%MATILDE%' OR `NETOS`.nome LIKE '%MATILDE%'
This query will query the three tables PAIS, FILHOS, NETOS by searching for '%MATILDE%' in the name field
Now I want to do like in that example but with if
SE encontrar '%MATILDE%' na tabela NETOS CONCATENAR
CONCAT(' É FILHA DE ', FILHOS.nome),
CONCAT(' E NETA DE ', PAIS.nome)
SE encontrar '%MATILDE%' na tabela PAIS CONCATENAR
CONCAT(' É MAE DE ', FILHOS.nome),
CONCAT(' E AVO DE ', NETOS.nome)
I want to implement something like that.
3 answers
In the query type you intend to do, you can resolve with a subquery:
set @nome:='MATILDE';
SELECT CONCAT(' É MÃE DE ', f.nome) as filha,
CONCAT(' É FILHA DE ', p.nome) as mae,
CONCAT(' É AVÓ DE ',(SELECT nome from PAIS where nome like '%@nome%')) as neta,
CONCAT(' É NETA DE ',p.nome) as avo,
FROM `PAIS` p,
`FILHOS` f,
`NETOS` n
WHERE `p`.nome LIKE '%@nome%'
OR `f`.nome LIKE '%@nome%'
OR `n`.nome LIKE '%@nome%';
And use the condition IF to ignore concatenation, since there may not always be daughters, granddaughters, mothers, and grandparents in the same database, you can make a filter:
SELECT IF(f.nome IS NOT NULL, CONCAT(' É MÃE DE ', f.nome), null) as filha,
IF(p.nome IS NOT NULL, CONCAT(' É FILHA DE ', p.nome), null) as mae,
IF((SELECT nome from NETOS where nome like '%@nome%') IS NOT NULL, CONCAT(' É AVÓ DE ',(SELECT nome from NETOS where nome like '%@nome%'), null) as neta,
IF(p.nome IS NOT NULL, CONCAT(' É NETA DE ',p.nome),null) as avo,
FROM `PAIS` p,
`FILHOS` f,
`NETOS` n
WHERE `p`.nome LIKE '%@nome%'
OR `f`.nome LIKE '%@nome%'
OR `n`.nome LIKE '%@nome%';
Note: your question was confused, because in the relationship you present, it was not clear what differentiates the "granddaughters" in the relationship between parents and daughters. For granddaughters can be as much mothers as they can be son. Even if I try the same last name, a mother may come as her own granddaughter if she considers only the relationship of name.
In SQL it is possible to use case ELSE
CASE WHEN aaa = bbb THEN xxx ELSE zzzz END
Example:
SELECT a, b, c, (CASE WHEN aaa = bbb THEN xxx ELSE zzzz END) AS d
FROM table
Is compatible with SQL 92, works on SQL SERVER and MySQL
This is a function almost equal to what exists in Excel, or as we are more accustomed in programming, it works analogous to a conditional operator. The ELSE is consequence because the first argument of the function is the condition, the second is the expression it should return if the condition is true, and the third argument is what it will return if the condition is false, carrying what "would be executed" in the ELSE.
Then it is not used as control flow effectively, but only as part of a calculation of values (it's like the conditional operator). You can only do simple things.
In Bacco's comment has actual usage example .
Until there is the IF/ELSE for Flow Control , but not to use in SQL queries, here it is to program functions and procedures .
There you can see that you can use a CASE also that works in a similar way, only that you can have several options, that is, for each value found in the main expression (probably a column) there will be the return of another corresponding value. it is possible to use a ELSE in it as well.
So I wouldn't call it a rumor, it was information passed on by someone to give greater grounds and references. Whenever possible consult the official documentation. Or ask here:)