• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

IN statment

Guest
Feb 11, 2014 Feb 11, 2014

Copy link to clipboard

Copied

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?

Views

1.3K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 11, 2014 Feb 11, 2014

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Feb 11, 2014 Feb 11, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 12, 2014 Feb 12, 2014

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Feb 12, 2014 Feb 12, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 12, 2014 Feb 12, 2014

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Feb 12, 2014 Feb 12, 2014

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 12, 2014 Feb 12, 2014

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Feb 19, 2014 Feb 19, 2014

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Feb 12, 2014 Feb 12, 2014

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 20, 2014 Feb 20, 2014

Copy link to clipboard

Copied

LATEST

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'))

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation