10 Replies Latest reply on Feb 20, 2014 5:03 AM by BKBK

    IN statment

    Sergey_S

      I have the table:

         

      IDcondition_id
      property_id
      1c_3b_1
      2c_13b_2
      3c_3b_4
      4c_13b_1
      5c_3b_2
      6c_4b_1

       

      I need to select all properties with condition_id c_3 and c_13 not just c_3 or c_13 (I expect in the result it should be only b_1 and b_2)

       

      The query like this:

       

      SELECT *

      FROM table

      WHERE condition_id IN (  3,13                                                            )

       

      The result (b_1, b_2, b_4)

       

      What do I do wrong?

        • 1. Re: IN statment
          REEDPOWELL Level 1

          Can you show us your exact cfquery statement?  Hard to believe those results!  The actual syntax you want would be:

          SELECT *

          FROM table

          WHERE condition_id='c_3' AND condition_ID='c_13')

           

          The IN operator is just a shortcut for a bunch of OR operators.

          hth

          Reed

          • 2. Re: IN statment
            Sergey_S Level 1

            Thank you REEDPOWELL

            I did try this:

             

            SELECT *

            FROM table

            WHERE

                                                                        <cfloop list="#condition_id#" index="iii">

                                                                         condition_id=#iii#<cfif iii is "#trim(ListLast(condition_id))#"><cfelse> and </cfif>

                                                                        </cfloop>

             

            Or to make clear, exactly as you are suggesting this:

            SELECT *

            FROM table

            WHERE condition_id='c_3' and condition_id='c_13'

             

            But return is 0 records.

            • 3. Re: IN statment
              Fernis Level 3

              Your question (or English) was a bit vague, but here goes:

               

              If you want just c_3 and c_13's returned

               

              SELECT id,condition_id,property_id

              FROM table

              WHERE condition_id = 'c_3' OR condition_id = 'c_13'

               

              But it seemed you wanted to filter out the results further by having only c_3 and c_13's which also match b_1 or b_2

               

              SELECT id,condition_id,property_id

              FROM table

              WHERE (condition_id = 'c_3' OR condition_id = 'c_13')

              AND (property_id = 'b_1' OR property_id = 'b_2')

               

              Alternatively,

               

              SELECT id,condition_id,property_id

              FROM table

              WHERE condition_id IN ('c_3,'c_13')

              AND property_id IN ('b_1','b_2')

               

              -Fernis

              • 4. Re: IN statment
                REEDPOWELL Level 1

                Two things:  One, the CFLOOP code is not equivalent to the code I had written. I cannot honestly tell you exactly what that the CFLOOP code is producing.

                 

                But the 2nd bit of code you showed should work, so I have to ask at this point - have you tried executing this code in SSMS, and does it work there?  There is something else going on here, probably with the values in your table.  Are those varchar() columns or char() columns?  Are there trailing spaces that need to be trimmed during the compare operations?  First step is to verify that it works in SSMS before trying to find the CF code bug.

                -reed

                • 5. Re: IN statment
                  Sergey_S Level 1

                  Yes, I did run it in the SSMS, the same result, 0 records.

                   

                  Here is actual table:

                   

                  activities_bus_idint
                  activities_bus_namenvarchar(250)
                  activities_bus_busidint
                  activities_bus_amidint

                   

                   

                  And data:

                                                                                                                                          

                  activities_bus_idactivities_bus_nameactivities_bus_amidactivities_bus_busid
                  16Golf13 9
                  20Fishing39
                  48Golf1313
                  49Fishing313
                  42Fishing310

                   

                  I need to select companies (activities_bus_busid) who is doing Golf and Fishing (not just Golf or Fishing) 

                  activities_bus_amid=13 and activities_bus_amid=3

                   

                  The result I'm getting is 0 records.

                   

                  The query I use:

                  <cfquery datasource="#dsn#" name="all_for_links_q">

                      SELECT activities_bus_busid

                      FROM activities_bus_tb

                      WHERE activities_bus_name='Fishing' and activities_bus_name='Beach'

                  </cfquery>

                   

                  and the same by id's:

                  <cfquery datasource="#dsn#" name="all_for_links_q">

                      SELECT activities_bus_busid

                      FROM activities_bus_tb

                      WHERE activities_bus_amid=3 and activities_bus_amid=13

                  </cfquery>

                   

                  If I use IN I'm getting all 3 companies in return (9,13 and 10)

                  If I use AND, result is 0 records.

                  • 6. Re: IN statment
                    REEDPOWELL Level 1

                    ok, seeing the data makes all the difference.  Also, I think that your description of what you were looking for was not quite correct.  What you want are all records with and amid ID of 3 and all records with and amid ID of 13, right?  In other words, it is the AND of the two sets, not the AND of the two operators.   In that case (no pun) you want to use the IN operator.  The AND will always return 0 records because it is impossible for a single record to meet both of those criteria.  What you really want is the result of the OR of the operators - any record that meets any of the conditions.  That's what the IN operator is a shortcut for.

                    -reed

                    • 7. Re: IN statment
                      Sergey_S Level 1

                      Sorry if I wasn't clear enough. 

                       

                      What I need is to select companies (activities_bus_busid) who is doing both Fishing and Golf. In our case it is companies 9 and 13

                      If I use IN operator I'm getting all 3 companies in the result (9,13,10) , as you mention earlier IN is working the same way as OR ( WHERE activities_bus_amid=3 or activities_bus_amid=13)

                       

                      The simple question is, How to select companies who is doing Fishing and Golf not one of it but both?

                      • 8. Re: IN statment
                        REEDPOWELL Level 1

                        ok.  wow.  interesting table design for this purpose.  Here is what you are going to have to do, more or less (ie, I have not done this in SSMS for you, so it might need some fine tuning):

                         

                        SELECT COUNT(*) as TOT,id from

                        (

                             select ACCOUNT_bus_id, Account_bus_name,Account_bus_amid AS ID

                             FROM activities_bus_tb

                        UNION ALL

                            

                        select ACCOUNT_bus_id, Account_bus_name,Account_bus_busid AS ID

                             FROM activities_bus_tb

                        )

                        HAVING COUNT(*) > 1

                        GROUP BY ID

                        ORDER BY ID

                         

                        this should give you the list of IDs that are in multiple records.  You can then feed that list into the orginial query's IN statement to pull out the rest of the data related to those IDs that appear in 2 places.

                         

                        The problem with the table design is that you have two columns that hold the same object (the ID value).  Normalizing the table so that there is just one ID column instead of two, and then having another table that contains the IDs and also some indication as to whether it is a AMID or a BUSID would make it possible to do this in a simpler query.

                         

                        good luck!

                        -reed

                        • 9. Re: IN statment
                          Sergey_S Level 1

                          Thank you REEDPOWELL !

                           

                          It give me the way. I did it a little bit different and it's working.

                           

                          SELECT activities_bus_busid, COUNT(activities_bus_busid) countids

                                                        FROM activities_bus_tb

                                                                            WHERE activities_bus_amid IN (3,13)

                                                                            GROUP BY activities_bus_busid

                                                                            HAVING COUNT(activities_bus_busid)=2

                          • 10. Re: IN statment
                            BKBK Adobe Community Professional & MVP

                            Sergey_S wrote:

                             

                            I have the table:

                               

                            IDcondition_id
                            property_id
                            1c_3b_1
                            2c_13b_2
                            3c_3b_4
                            4c_13b_1
                            5c_3b_2
                            6c_4b_1

                             

                            I need to select all properties with condition_id c_3 and c_13 not just c_3 or c_13 (I expect in the result it should be only b_1 and b_2)

                             

                            The query like this:

                             

                            SELECT *

                            FROM table

                            WHERE condition_id IN (  3,13                                                            )

                            A simple way to do it is:

                             

                            SELECT *

                            FROM table

                            WHERE condition_id IN ('c_3') AND property_id IN (SELECT property_id FROM table WHERE condition_id IN ('c_13'))