Your syntax would output like this:
where 15 IN (sharedGroupIds)
If I'm reading your right this is backwards - it should be "column IN values" or sharedGroupIds IN (15)
Unfortunatley the IN clause is not going to work for you with a column of values because it uses "equals" against each
value. Consider this scenario:
Groupids contains "15,17,7,9"
You have a single groupID of 17. You would think that groupids IN (17) would return true - nope..because "15,17,7,9"
does NOT equal "17". SQL is not going to treat a single column with delimited lists in it as if it were a query or
group of values. To SQL "15,17,7,9" is a single value - not 4 values.
Now, you can use "like" to maybe futz with this so you could do
where groupids LIKE ('%,17,%) OR groupids LIKE (17,%) or groupIds LIKE (%,17)
But that is a poor excuse for a query (ha). You have to account for wether the 17 is in the middle, end or
beginning...AND that is still not perfect.
A better choice would probably be a second table of groupids mapping your permissions....then your query becomes
where groupid IN
(select groupId from relatedGroups where groupid IN (17,2,0) )
Or you can join it as well...
I hope this helps.
-Mark
-----Original Message-----
From: Burns, John D [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 19, 2004 11:24 AM
To: CF-Talk
Subject: MS SQL Syntax/Keyword
I have a table in MS SQL with a column that has a groupID value in it
(int) and a column that is sharedGroupIDs (varchar). The groupID holds
an int of the group that the record belongs to. The sharedGroupIDs
holds a string of IDs that also can see the record. I'm trying to use
the IN operator to find all rows where the current GroupID is either the
groupID on a row or is IN the sharedGroupIDs column for that row.
Here's 2 separate syntaxes I've used and the results I've gotten:
select *
from table
where #groupID# = groupID or #groupID# IN (sharedGroupIDs)
The above returns an error because SQL says it can't convert the
SharedGroupIDs column to type of INT. When reading about the IN
operator, it says that the datatypes you're comparing have to match, so
I tried the following:
select *
from table
where #groupID# = groupID or '#groupID#' IN (sharedGroupIDs)
Notice the single quotes around #groupID# so that it should be seen as a
string. This doesn't produce and error, but it does not return any
records. A simple data test that I'm trying is #groupID# = 5 and in the
SharedGroupIDs column I have this string: 15,7,5
Any ideas on why this isn't working? Any suggestions? Maybe another
operator
Also, just to stop unneeded responses, I can't use LIKE because if the
value I'm searching for is 1 is and 15 is in the column, it will return
true because there is a 1 and that's not what I'm looking for.
Please help! :-)
John Burns
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
