Mixing
If the word is in two columns then priority in the results in mysql
I have a table like these:
menu_id menu_name menu_description
i would like to order the results giving a priority if the word is in the menu_name and menu description. How can i achieve this?
UPDATE:
with this query i get the most relevant results on top:
select menu_id, menu_name, menu_description from ( select menu_id,
menu_name, menu_description, case when menu_name like '%salame%'
then 1 else 0 end + case when menu_description like '%salame%'
then 1 else 0 end as order_value from z8upvan6w_menus) as t order
by order_value desc
results:
menu_id | menu_name | menu_description
13 | Panino Salame e Noci | Salame, Noci, Caprino e Insalata
14 | Piadina Salame e Noci | Salame, Noci, Caprino e Insalata
11 | Panino Caprese | Mozzarella, Pomodoro e Insalata
12 | Panino Boscaiolo | Speck, Brie e Salsa Boscaiola
my question is why the third and fourth record appear if there is no word "salame" in any field, how do i get rid of them?
jquery mysql
asked Nov 22 '18 at 1:12
Robert FalcoRobert Falco
12
add a comment |
I have a table like these:
menu_id menu_name menu_description
i would like to order the results giving a priority if the word is in the menu_name and menu description. How can i achieve this?
UPDATE:
with this query i get the most relevant results on top:
select menu_id, menu_name, menu_description from ( select menu_id,
menu_name, menu_description, case when menu_name like '%salame%'
then 1 else 0 end + case when menu_description like '%salame%'
then 1 else 0 end as order_value from z8upvan6w_menus) as t order
by order_value desc
results:
menu_id | menu_name | menu_description
13 | Panino Salame e Noci | Salame, Noci, Caprino e Insalata
14 | Piadina Salame e Noci | Salame, Noci, Caprino e Insalata
11 | Panino Caprese | Mozzarella, Pomodoro e Insalata
12 | Panino Boscaiolo | Speck, Brie e Salsa Boscaiola
my question is why the third and fourth record appear if there is no word "salame" in any field, how do i get rid of them?
jquery mysql
asked Nov 22 '18 at 1:12
Robert FalcoRobert Falco
12
1
What word? What is themenu_name? What is the "menu description"?
– Gordon Linoff
Nov 22 '18 at 1:28
I updated the question, please have a look.
– Robert Falco
Nov 22 '18 at 2:42
Because the records are only ordered, not filtered. I'll update this in my answer below.
– Yatin
Nov 22 '18 at 22:50
add a comment |
I have a table like these:
menu_id menu_name menu_description
i would like to order the results giving a priority if the word is in the menu_name and menu description. How can i achieve this?
UPDATE:
with this query i get the most relevant results on top:
select menu_id, menu_name, menu_description from ( select menu_id,
menu_name, menu_description, case when menu_name like '%salame%'
then 1 else 0 end + case when menu_description like '%salame%'
then 1 else 0 end as order_value from z8upvan6w_menus) as t order
by order_value desc
results:
menu_id | menu_name | menu_description
13 | Panino Salame e Noci | Salame, Noci, Caprino e Insalata
14 | Piadina Salame e Noci | Salame, Noci, Caprino e Insalata
11 | Panino Caprese | Mozzarella, Pomodoro e Insalata
12 | Panino Boscaiolo | Speck, Brie e Salsa Boscaiola
my question is why the third and fourth record appear if there is no word "salame" in any field, how do i get rid of them?
jquery mysql
asked Nov 22 '18 at 1:12
Robert FalcoRobert Falco
12
I have a table like these:
menu_id menu_name menu_description
i would like to order the results giving a priority if the word is in the menu_name and menu description. How can i achieve this?
UPDATE:
with this query i get the most relevant results on top:
select menu_id, menu_name, menu_description from ( select menu_id,
menu_name, menu_description, case when menu_name like '%salame%'
then 1 else 0 end + case when menu_description like '%salame%'
then 1 else 0 end as order_value from z8upvan6w_menus) as t order
by order_value desc
results:
menu_id | menu_name | menu_description
13 | Panino Salame e Noci | Salame, Noci, Caprino e Insalata
14 | Piadina Salame e Noci | Salame, Noci, Caprino e Insalata
11 | Panino Caprese | Mozzarella, Pomodoro e Insalata
12 | Panino Boscaiolo | Speck, Brie e Salsa Boscaiola
my question is why the third and fourth record appear if there is no word "salame" in any field, how do i get rid of them?
jquery mysql
jquery mysql
asked Nov 22 '18 at 1:12
Robert FalcoRobert Falco
12
asked Nov 22 '18 at 1:12
Robert FalcoRobert Falco
12
edited Nov 22 '18 at 2:43
Robert Falco
asked Nov 22 '18 at 1:12
Robert FalcoRobert Falco
12
asked Nov 22 '18 at 1:12
Robert FalcoRobert Falco
12
asked Nov 22 '18 at 1:12
Robert FalcoRobert Falco
12
12
1
What word? What is themenu_name? What is the "menu description"?
– Gordon Linoff
Nov 22 '18 at 1:28
I updated the question, please have a look.
– Robert Falco
Nov 22 '18 at 2:42
Because the records are only ordered, not filtered. I'll update this in my answer below.
– Yatin
Nov 22 '18 at 22:50
add a comment |
1
What word? What is themenu_name? What is the "menu description"?
– Gordon Linoff
Nov 22 '18 at 1:28
I updated the question, please have a look.
– Robert Falco
Nov 22 '18 at 2:42
Because the records are only ordered, not filtered. I'll update this in my answer below.
– Yatin
Nov 22 '18 at 22:50
1
1
What word? What is the
menu_name? What is the "menu description"?– Gordon Linoff
Nov 22 '18 at 1:28
What word? What is the
menu_name? What is the "menu description"?– Gordon Linoff
Nov 22 '18 at 1:28
I updated the question, please have a look.
– Robert Falco
Nov 22 '18 at 2:42
I updated the question, please have a look.
– Robert Falco
Nov 22 '18 at 2:42
Because the records are only ordered, not filtered. I'll update this in my answer below.
– Yatin
Nov 22 '18 at 22:50
Because the records are only ordered, not filtered. I'll update this in my answer below.
– Yatin
Nov 22 '18 at 22:50
add a comment |
2 Answers
2
active
oldest
votes
You are not filtering out any records. For that, you need a where clause:
select menu_id, menu_name, menu_description
from from z8upvan6w_menus
where (menu_name like '%salame%') or
(menu_description like '%salame%' )
order by ( (menu_name like '%salame%') +
(menu_description like '%salame%')
) desc;
Notes:
- The
whereclause does the filtering. - No subquery is necessary. You can order by an expression.
- I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.
answered Nov 22 '18 at 4:11
Gordon LinoffGordon Linoff
778k35307410
add a comment |
I'd think you can use a dynamic numeric order with value 1 when the name is in the menu_name and 1 when the name is in menu_description. Sum them up when you select and order the results by the summed up value.
Like this:
It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.
UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.
Assuming @input_val is the value you're trying to find in the columns.
select menu_id, menu_name, menu_description
from (
select menu_id, menu_name, menu_description,
case
when menu_name like '%' + @input_val + '%' then 1
else 0
end
+
case
when menu_description like '%' + @input_val + '%' then 1
else 0
end
as order_value
from menu_table) as t
where t.order_value > 0
order by order_value desc, menu_name asc
answered Nov 22 '18 at 1:53
YatinYatin
978712
thank you @Yatin, please read the update.
– Robert Falco
Nov 22 '18 at 2:41
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53422605%2fif-the-word-is-in-two-columns-then-priority-in-the-results-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You are not filtering out any records. For that, you need a where clause:
select menu_id, menu_name, menu_description
from from z8upvan6w_menus
where (menu_name like '%salame%') or
(menu_description like '%salame%' )
order by ( (menu_name like '%salame%') +
(menu_description like '%salame%')
) desc;
Notes:
- The
whereclause does the filtering. - No subquery is necessary. You can order by an expression.
- I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.
answered Nov 22 '18 at 4:11
Gordon LinoffGordon Linoff
778k35307410
add a comment |
You are not filtering out any records. For that, you need a where clause:
select menu_id, menu_name, menu_description
from from z8upvan6w_menus
where (menu_name like '%salame%') or
(menu_description like '%salame%' )
order by ( (menu_name like '%salame%') +
(menu_description like '%salame%')
) desc;
Notes:
- The
whereclause does the filtering. - No subquery is necessary. You can order by an expression.
- I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.
answered Nov 22 '18 at 4:11
Gordon LinoffGordon Linoff
778k35307410
add a comment |
You are not filtering out any records. For that, you need a where clause:
select menu_id, menu_name, menu_description
from from z8upvan6w_menus
where (menu_name like '%salame%') or
(menu_description like '%salame%' )
order by ( (menu_name like '%salame%') +
(menu_description like '%salame%')
) desc;
Notes:
- The
whereclause does the filtering. - No subquery is necessary. You can order by an expression.
- I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.
answered Nov 22 '18 at 4:11
Gordon LinoffGordon Linoff
778k35307410
You are not filtering out any records. For that, you need a where clause:
select menu_id, menu_name, menu_description
from from z8upvan6w_menus
where (menu_name like '%salame%') or
(menu_description like '%salame%' )
order by ( (menu_name like '%salame%') +
(menu_description like '%salame%')
) desc;
Notes:
- The
whereclause does the filtering. - No subquery is necessary. You can order by an expression.
- I revised the expression so it uses the fact the MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.
answered Nov 22 '18 at 4:11
Gordon LinoffGordon Linoff
778k35307410
answered Nov 22 '18 at 4:11
Gordon LinoffGordon Linoff
778k35307410
answered Nov 22 '18 at 4:11
Gordon LinoffGordon Linoff
778k35307410
answered Nov 22 '18 at 4:11
Gordon LinoffGordon Linoff
778k35307410
778k35307410
add a comment |
add a comment |
I'd think you can use a dynamic numeric order with value 1 when the name is in the menu_name and 1 when the name is in menu_description. Sum them up when you select and order the results by the summed up value.
Like this:
It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.
UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.
Assuming @input_val is the value you're trying to find in the columns.
select menu_id, menu_name, menu_description
from (
select menu_id, menu_name, menu_description,
case
when menu_name like '%' + @input_val + '%' then 1
else 0
end
+
case
when menu_description like '%' + @input_val + '%' then 1
else 0
end
as order_value
from menu_table) as t
where t.order_value > 0
order by order_value desc, menu_name asc
answered Nov 22 '18 at 1:53
YatinYatin
978712
thank you @Yatin, please read the update.
– Robert Falco
Nov 22 '18 at 2:41
add a comment |
I'd think you can use a dynamic numeric order with value 1 when the name is in the menu_name and 1 when the name is in menu_description. Sum them up when you select and order the results by the summed up value.
Like this:
It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.
UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.
Assuming @input_val is the value you're trying to find in the columns.
select menu_id, menu_name, menu_description
from (
select menu_id, menu_name, menu_description,
case
when menu_name like '%' + @input_val + '%' then 1
else 0
end
+
case
when menu_description like '%' + @input_val + '%' then 1
else 0
end
as order_value
from menu_table) as t
where t.order_value > 0
order by order_value desc, menu_name asc
answered Nov 22 '18 at 1:53
YatinYatin
978712
thank you @Yatin, please read the update.
– Robert Falco
Nov 22 '18 at 2:41
add a comment |
I'd think you can use a dynamic numeric order with value 1 when the name is in the menu_name and 1 when the name is in menu_description. Sum them up when you select and order the results by the summed up value.
Like this:
It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.
UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.
Assuming @input_val is the value you're trying to find in the columns.
select menu_id, menu_name, menu_description
from (
select menu_id, menu_name, menu_description,
case
when menu_name like '%' + @input_val + '%' then 1
else 0
end
+
case
when menu_description like '%' + @input_val + '%' then 1
else 0
end
as order_value
from menu_table) as t
where t.order_value > 0
order by order_value desc, menu_name asc
answered Nov 22 '18 at 1:53
YatinYatin
978712
I'd think you can use a dynamic numeric order with value 1 when the name is in the menu_name and 1 when the name is in menu_description. Sum them up when you select and order the results by the summed up value.
Like this:
It's written according to the SQL Server syntax, but I think you should be able to convert it the way you want.
UPDATE: Adding where clause to the query below to filter out the records which don't match both the menu_name and menu_description.
Assuming @input_val is the value you're trying to find in the columns.
select menu_id, menu_name, menu_description
from (
select menu_id, menu_name, menu_description,
case
when menu_name like '%' + @input_val + '%' then 1
else 0
end
+
case
when menu_description like '%' + @input_val + '%' then 1
else 0
end
as order_value
from menu_table) as t
where t.order_value > 0
order by order_value desc, menu_name asc
answered Nov 22 '18 at 1:53
YatinYatin
978712
edited Nov 22 '18 at 22:53
answered Nov 22 '18 at 1:53
YatinYatin
978712
answered Nov 22 '18 at 1:53
YatinYatin
978712
answered Nov 22 '18 at 1:53
YatinYatin
978712
978712
thank you @Yatin, please read the update.
– Robert Falco
Nov 22 '18 at 2:41
add a comment |
thank you @Yatin, please read the update.
– Robert Falco
Nov 22 '18 at 2:41
thank you @Yatin, please read the update.
– Robert Falco
Nov 22 '18 at 2:41
thank you @Yatin, please read the update.
– Robert Falco
Nov 22 '18 at 2:41
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53422605%2fif-the-word-is-in-two-columns-then-priority-in-the-results-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What word? What is the
menu_name? What is the "menu description"?– Gordon Linoff
Nov 22 '18 at 1:28
I updated the question, please have a look.
– Robert Falco
Nov 22 '18 at 2:42
Because the records are only ordered, not filtered. I'll update this in my answer below.
– Yatin
Nov 22 '18 at 22:50