Query with where clause checking a variable with multiple values

Multi tool use
Multi tool use












0















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)









share|improve this question



























    0















    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)









    share|improve this question

























      0












      0








      0








      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)









      share|improve this question














      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






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Dec 29 '18 at 16:09









      DGMS89DGMS89

      5341835




      5341835
























          2 Answers
          2






          active

          oldest

          votes


















          1














          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)





          share|improve this answer































            0














            You are missing ' ' for the two brackets ()



            variable should be set like this-



            set @idlist1 = '("Owner","Admin","Rule")'





            share|improve this answer
























            • 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











            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
            });


            }
            });














            draft saved

            draft discarded


















            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









            1














            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)





            share|improve this answer




























              1














              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)





              share|improve this answer


























                1












                1








                1







                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)





                share|improve this answer













                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)






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Dec 29 '18 at 17:17









                GMBGMB

                8,3862623




                8,3862623

























                    0














                    You are missing ' ' for the two brackets ()



                    variable should be set like this-



                    set @idlist1 = '("Owner","Admin","Rule")'





                    share|improve this answer
























                    • 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
















                    0














                    You are missing ' ' for the two brackets ()



                    variable should be set like this-



                    set @idlist1 = '("Owner","Admin","Rule")'





                    share|improve this answer
























                    • 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














                    0












                    0








                    0







                    You are missing ' ' for the two brackets ()



                    variable should be set like this-



                    set @idlist1 = '("Owner","Admin","Rule")'





                    share|improve this answer













                    You are missing ' ' for the two brackets ()



                    variable should be set like this-



                    set @idlist1 = '("Owner","Admin","Rule")'






                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    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



















                    • 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


















                    draft saved

                    draft discarded




















































                    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.




                    draft saved


                    draft discarded














                    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





















































                    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
                    JjHuhdV4cI50OLh8E2qetBeWOIbE Xm1SveSZkBAmnGEBEZ,GknwzSJtqUOD6Zm J2YNmbsSzQ,8PqjSTuSTb

                    Popular posts from this blog

                    Monofisismo

                    Angular Downloading a file using contenturl with Basic Authentication

                    Olmecas