There is a table, in it - many fields.

I want to select all the fields where the поле_имени matches my text, in the same query I want to pull out all the fields where the поле_описания matches the same text.

How to do it? Or at least show in which direction to dig =)

Requests themselves:

 SELECT * FROM table_goods WHERE name LIKE '%искомый_текст%' SELECT * FROM table_goods WHERE descr LIKE '%искомый_текст%'

I just need the result of these queries to be common, but that the 1st half of the result was with the match in the name, and the 2nd half with the match in the description.

For example,

 id "1", name "мандарин", descr "с марковкой хорошо" id "2", name "марковка", descr "описание" id "3", name "огурцы-1", descr "сочетается с молоком" id "4", name "помидоры", descr "с мясом хорошо сочетается" id "5", name "марковный_сок", descr "описание"

We execute the query LIKE '% Mark%' first by name then by description and as a result we want to get:

 id "2", name "марковка", descr "описание" id "5", name "марковный_сок", descr "описание" id "1", name "мандарин", descr "с марковкой хорошо"

First displayed the results by name, then by description.

Help make one request, please.

  • 2
    See UNION operator. - Yaant
  • @Yaant, I'll take a look - zayn1991
  • one
    UNION is undesirable - this is an unconditional additional and not very necessary sorting. - Akina
  • @Akina Well, after LIKE %...% overhead sorting can be almost neglected. :) And if non-critical duplication of records that have the search text at the same time in the title and in the description, then you can use UNION ALL - Yaant
  • This is true, but with an eye to the amount of data. Still, Like works with one field, and sorting will work with the whole record. Again, Like can be made to perform an index scan, if there is one. So it may turn out that the overhead projector is not the same and voluminous. The rest - see comments to the post Nikolay Baranenko . - Akina

3 answers 3

 SELECT * FROM table_goods WHERE name LIKE '%искомый_текст%' OR descr LIKE '%искомый_текст%' -- ORDER BY (name LIKE '%искомый_текст%') DESC
  • and desc for what? - zayn1991
  • @ zayn1991 to sort the data in descending order - MrFylypenko
  • @Akina, checked, works fine) - zayn1991
  • @ zayn1991 If the condition is true, the comparison gives True, which is converted to numeric 1, and if not, False, which is 0. Since we need to first match the name - first we need 1, and then 0, therefore DESC. - Akina

try this

 SELECT id, name, descr FROM table_goods WHERE name LIKE '%искомый_текст%' UNION SELECT id, name, descr FROM table_goods WHERE descr LIKE '%искомый_текст%'
  • "UNION is undesirable - this is an unconditional additional and not very necessary sorting. - Akina" - zayn1991
  • Thank you for the answer, but I will have an overall table, so it will not work for me, there will be a lot of iterations so that the records are not duplicated, and I implement the android - zayn1991
  • It is more reasonable UNION ALL, and in the second query add AND NOT (name LIKE '% search_text%'). The effect is the same, but the sorting will be replaced by additional selection, which is faster and cheaper. And if index arrow is still shooting ... - Akina

I suppose such a request will solve this problem:

 SELECT * FROM table_goods WHERE name LIKE '%марков%' OR descr LIKE '%марков%' ORDER BY (CASE WHEN name LIKE '%марков%' THEN 1 ELSE 2 END)
  • "CASE WHEN THEN 1 ELSE 2 END" what is it? never came across - zayn1991
  • This is a sort by condition. Allows for more complex sorting. For example, first output the results where name = искомый_текст followed by those in which name LIKE %искомый_текст% , and then those in which descr LIKE %искомый_текст% : ODER BY (CASE WHEN name = 'марковка' THEN 1 WHEN name LIKE '%марковка%' THEN 2 ELSE 3 END) - Pyramidhead
  • it works, but these commands are new to me, if I need to edit it - I cannot work with these commands with my level. Do you and Akina have the same implementation? - zayn1991
  • Yes, this is actually the same implementation. Just in this variant one more calculation is added - in this case unnecessary. - Akina