Query with where clause checking a variable with multiple values

Multi tool use
Scenario: I am trying to create a query where I want to retrieve multiple values from a table. Doing so by listing the values directly is straightforward, like so:
select * from product.text_data
where text_data.field_label = "Owner" or text_data.field_label = "Admin" or text_data.field_label = "Rule";
Question: Is it possible to do the same query, but instead feeding the list of fields into a variable, and checking the variable directly?
Ex:
set @idlist1 = ("Owner", "Admin", "Rule");
select * from product.text_data
where product.text_data.field_ref in @idlist1;
Issue: If I try to run this, I get the
SQL Error (1241): Operand should contain 1 column(s)
mysql heidisql
add a comment |
Scenario: I am trying to create a query where I want to retrieve multiple values from a table. Doing so by listing the values directly is straightforward, like so:
select * from product.text_data
where text_data.field_label = "Owner" or text_data.field_label = "Admin" or text_data.field_label = "Rule";
Question: Is it possible to do the same query, but instead feeding the list of fields into a variable, and checking the variable directly?
Ex:
set @idlist1 = ("Owner", "Admin", "Rule");
select * from product.text_data
where product.text_data.field_ref in @idlist1;
Issue: If I try to run this, I get the
SQL Error (1241): Operand should contain 1 column(s)
mysql heidisql
add a comment |
Scenario: I am trying to create a query where I want to retrieve multiple values from a table. Doing so by listing the values directly is straightforward, like so:
select * from product.text_data
where text_data.field_label = "Owner" or text_data.field_label = "Admin" or text_data.field_label = "Rule";
Question: Is it possible to do the same query, but instead feeding the list of fields into a variable, and checking the variable directly?
Ex:
set @idlist1 = ("Owner", "Admin", "Rule");
select * from product.text_data
where product.text_data.field_ref in @idlist1;
Issue: If I try to run this, I get the
SQL Error (1241): Operand should contain 1 column(s)
mysql heidisql
Scenario: I am trying to create a query where I want to retrieve multiple values from a table. Doing so by listing the values directly is straightforward, like so:
select * from product.text_data
where text_data.field_label = "Owner" or text_data.field_label = "Admin" or text_data.field_label = "Rule";
Question: Is it possible to do the same query, but instead feeding the list of fields into a variable, and checking the variable directly?
Ex:
set @idlist1 = ("Owner", "Admin", "Rule");
select * from product.text_data
where product.text_data.field_ref in @idlist1;
Issue: If I try to run this, I get the
SQL Error (1241): Operand should contain 1 column(s)
mysql heidisql
mysql heidisql
asked Dec 29 '18 at 16:09


DGMS89DGMS89
5341835
5341835
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
It is not possible to define a list as a variable. You would have to declare your variable as a temporary table.
In your use case, you seem to be looking for dynamic sql.
declare @myList varchar(100)
set @myList = '( "Owner", "Admin", "Rule" )'
exec('SELECT * FROM product.text_data WHERE product.text_data.field_ref IN' + @myList)
add a comment |
You are missing ' ' for the two brackets ()
variable should be set like this-
set @idlist1 = '("Owner","Admin","Rule")'
Thanks for the answer. I just tried that, but got error 1064 at the "where" line.
– DGMS89
Dec 29 '18 at 17:01
I forget to mention ; at end of variable maybe you should check
– Kedar Limaye
Dec 29 '18 at 17:13
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%2f53971159%2fquery-with-where-clause-checking-a-variable-with-multiple-values%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
It is not possible to define a list as a variable. You would have to declare your variable as a temporary table.
In your use case, you seem to be looking for dynamic sql.
declare @myList varchar(100)
set @myList = '( "Owner", "Admin", "Rule" )'
exec('SELECT * FROM product.text_data WHERE product.text_data.field_ref IN' + @myList)
add a comment |
It is not possible to define a list as a variable. You would have to declare your variable as a temporary table.
In your use case, you seem to be looking for dynamic sql.
declare @myList varchar(100)
set @myList = '( "Owner", "Admin", "Rule" )'
exec('SELECT * FROM product.text_data WHERE product.text_data.field_ref IN' + @myList)
add a comment |
It is not possible to define a list as a variable. You would have to declare your variable as a temporary table.
In your use case, you seem to be looking for dynamic sql.
declare @myList varchar(100)
set @myList = '( "Owner", "Admin", "Rule" )'
exec('SELECT * FROM product.text_data WHERE product.text_data.field_ref IN' + @myList)
It is not possible to define a list as a variable. You would have to declare your variable as a temporary table.
In your use case, you seem to be looking for dynamic sql.
declare @myList varchar(100)
set @myList = '( "Owner", "Admin", "Rule" )'
exec('SELECT * FROM product.text_data WHERE product.text_data.field_ref IN' + @myList)
answered Dec 29 '18 at 17:17


GMBGMB
8,3862623
8,3862623
add a comment |
add a comment |
You are missing ' ' for the two brackets ()
variable should be set like this-
set @idlist1 = '("Owner","Admin","Rule")'
Thanks for the answer. I just tried that, but got error 1064 at the "where" line.
– DGMS89
Dec 29 '18 at 17:01
I forget to mention ; at end of variable maybe you should check
– Kedar Limaye
Dec 29 '18 at 17:13
add a comment |
You are missing ' ' for the two brackets ()
variable should be set like this-
set @idlist1 = '("Owner","Admin","Rule")'
Thanks for the answer. I just tried that, but got error 1064 at the "where" line.
– DGMS89
Dec 29 '18 at 17:01
I forget to mention ; at end of variable maybe you should check
– Kedar Limaye
Dec 29 '18 at 17:13
add a comment |
You are missing ' ' for the two brackets ()
variable should be set like this-
set @idlist1 = '("Owner","Admin","Rule")'
You are missing ' ' for the two brackets ()
variable should be set like this-
set @idlist1 = '("Owner","Admin","Rule")'
answered Dec 29 '18 at 16:53
Kedar LimayeKedar Limaye
863412
863412
Thanks for the answer. I just tried that, but got error 1064 at the "where" line.
– DGMS89
Dec 29 '18 at 17:01
I forget to mention ; at end of variable maybe you should check
– Kedar Limaye
Dec 29 '18 at 17:13
add a comment |
Thanks for the answer. I just tried that, but got error 1064 at the "where" line.
– DGMS89
Dec 29 '18 at 17:01
I forget to mention ; at end of variable maybe you should check
– Kedar Limaye
Dec 29 '18 at 17:13
Thanks for the answer. I just tried that, but got error 1064 at the "where" line.
– DGMS89
Dec 29 '18 at 17:01
Thanks for the answer. I just tried that, but got error 1064 at the "where" line.
– DGMS89
Dec 29 '18 at 17:01
I forget to mention ; at end of variable maybe you should check
– Kedar Limaye
Dec 29 '18 at 17:13
I forget to mention ; at end of variable maybe you should check
– Kedar Limaye
Dec 29 '18 at 17:13
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%2f53971159%2fquery-with-where-clause-checking-a-variable-with-multiple-values%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
9Eq,fN,NAF Xp7fMHgPOZs4BcjfeRXvZOwkuLeL7,uVjH,NoAGs2LwgAcC8s,o3VLO,nrjtiRHx oaGAv9vZF gPj