Copy link to clipboard
Copied
I have the table:
ID | condition_id | property_id | |
---|---|---|---|
1 | c_3 | b_1 | |
2 | c_13 | b_2 | |
3 | c_3 | b_4 | |
4 | c_13 | b_1 | |
5 | c_3 | b_2 | |
6 | c_4 | b_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?
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
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.
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
Copy link to clipboard
Copied
Yes, I did run it in the SSMS, the same result, 0 records.
Here is actual table:
activities_bus_id | int |
activities_bus_name | nvarchar(250) |
activities_bus_busid | int |
activities_bus_amid | int |
And data:
activities_bus_id | activities_bus_name | activities_bus_amid | activities_bus_busid |
16 | Golf | 13 | 9 |
20 | Fishing | 3 | 9 |
48 | Golf | 13 | 13 |
49 | Fishing | 3 | 13 |
42 | Fishing | 3 | 10 |
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.
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
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?
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
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
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
Copy link to clipboard
Copied
Sergey_S wrote:
I have the table:
ID condition_id property_id 1 c_3 b_1 2 c_13 b_2 3 c_3 b_4 4 c_13 b_1 5 c_3 b_2 6 c_4 b_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'))