0
I’m setting up a job agency website and I have a search that searches the field title and description, what the person searched, as per the query below:
SELECT *
FROM vagas
WHERE (titulo LIKE '%termo_de_busca%' OR observacoes LIKE '%termo_de_busca%')
The query works fine and searches in the 2 columns, but I wanted to bring, first bring the results that has the "termo_de_busca" in the column title and then in the column Description.
How can I do that?
4
You’ll have to do for subquery:
SELECT * FROM
(
(SELECT * FROM vagas
WHERE titulo LIKE '%termo_de_busca%')
UNION ALL
(SELECT * FROM vagas
WHERE observacoes LIKE '%termo_de_busca%')
) as apelido
You’re just doing 2 selects, uniting them, and making one select in themselves. Finally, nicknamed this select of apelido.
Important: Whenever I make a union ( UNION) the 2 tables must have the same fields.
As stated by @Ricardopunctual in the comments, there may be flaw in the ordination of
UNION.
If you are not sorting correctly, or want to make sure it is, create a field tipo with the reference value:
SELECT * FROM
(
(SELECT *, 1 as tipo FROM vagas
WHERE titulo LIKE '%termo_de_busca%')
UNION ALL
(SELECT *, 2 as tipo FROM vagas
WHERE observacoes LIKE '%termo_de_busca%')
) as apelido
ORDER BY tipo
The results of the titulo the value of tipo will be 1 and of observacoes will be 2, and so you can order them.
Browser other questions tagged mysql select order-by
You are not signed in. Login or sign up in order to post.
would not be missing a
order byfor ensure the query brings in the order you want?– Ricardo Pontual
@Ricardopunctual I posted a new answer. But you gave me a question: when I do the
UNION, it will not bring the results in the sequence of the union?– rbz
Yeah, I thought so too, just like if I do a select with a column with identity, quence, auto_incremet or any automatically numbered key field, the result would always be ordered, but there is "no guarantee" of it. I even discussed it in an OR question and after searching, we actually confirmed that the bank does not guarantee an order, unless it is explicit in
order by, although I did several tests withsql serverandmysqland works this example you put (Union), as an automatic numbering field (sort by it)– Ricardo Pontual
I only commented because there may be some case that the bank returns in another order, and as not explicit the
order by, can’t blame the bank :)– Ricardo Pontual
Ahhh no Professor, I prefer to always comment! So I learn more! Then if you are there, give me the link of this question that you asked that I would like to see! And thank you for the teaching!
– rbz
I’ll test and already put the result, thanks!
– Leandro Marzullo
@Leandromarzullo Did the answer solve your problem? If not, we will fix what is needed.
– rbz
Yes, thank you, I accept the answer
– Leandro Marzullo