Re: Query finding data IN
In general, you don't want to store multiple values in a single cell...a relational database just isn't designed to handle data that way...there are string functions like charindex that can search through those fields, but the database engine is not optimized for that...a query that has to use one of those functions is going to take longer to return because it has to touch every record in the table. It might work all right with just a few records in the table, but it is going to start slowing down as more data is put in there. On 2/6/06, Eric J. Hoffman <[EMAIL PROTECTED]> wrote: > Since the are only 3 possible values and one session variable, it shouldn't > be that big of a deal? Seemed like a lot more overhead setting up pivot > tables or something similar... > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231561 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query finding data IN
Since the are only 3 possible values and one session variable, it shouldn't be that big of a deal? Seemed like a lot more overhead setting up pivot tables or something similar... Regards, Eric J. Hoffman Managing Partner EJH & Associates, LLC A Decision That Matters. EJHA. Eric J. Hoffman Managing Partner 2081 Industrial Blvd StillwaterMN55082 mail: [EMAIL PROTECTED] www: www.ejhassociates.com tel: 651.207.1526 fax: 651.207.1536 mob: 651.245.2717 This message contains confidential information and is intended only for [EMAIL PROTECTED] If you are not cf-talk@houseoffusion.com you should not disseminate, distribute or copy this e-mail. Please notify [EMAIL PROTECTED] immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Eric J. Hoffman therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Mon 2/6/2006 8:34 PM To: CF-Talk Subject: RE: Query finding data IN If you don't want to change the DB design you could try: SELECT * FROM YourTable WHERE YourColumn LIKE '%,#YourVariable#,%' OR YourColumn LIKE '#YourVariable#,%' OR YourColumn LIKE '%,#YourVariable#' Given a values like: 1,2,3,7,4,5,6 7,1,2,3,4,5,6 1,2,3,4,5,6,7 Looking for 7 it should match all three. Which seems like overkill, but looks like it'd work. Now I'm sure there might be a better way to do this. Personally I'd change the table design. Adrian -Original Message- From: Eric J. Hoffman [mailto:[EMAIL PROTECTED] Sent: 07 February 2006 02:01 To: CF-Talk Subject: Query finding data IN Have a query here I am not sure how to find the answer Have a field in the database with a comma list of items...and want it to only pull a record based upon a session variable that is a single integer. So how do I say give me records where database field(comma delimited) contains session variable? example: Where table.sites HAS session.variable I know has isn't the right term, but this is the english of it. Thanks. Eric J. Hoffman Managing Partner 2081 Industrial Blvd StillwaterMN55082 mail: [EMAIL PROTECTED] www: www.ejhassociates.com tel: 651.207.1526 fax: 651.207.1536 mob: 651.245.2717 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231558 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query finding data IN
Just to be certain, I ran some tests and confirmed that my remembrance was indeed incorrect. I was probably getting the direct use of a column mixed up with using a sub-query to pull values. I've been programming with abstract interfaces that shelter me from having to write my own queries too long and it's making my SQL knife dull. -Justin Scott > -Original Message- > From: Jim Wright [mailto:[EMAIL PROTECTED] > Sent: Monday, February 06, 2006 10:20 PM > To: CF-Talk > Subject: Re: Query finding data IN > > The IN operator is used the other way around, but won't help in this > scenario...if the possible values really are 1-9, then you could use > charindex...otherwise you might look at using patindex. > > On 2/6/06, Justin D. Scott <[EMAIL PROTECTED]> wrote: > > > Where table.sites HAS session.variable I know > > > has isn't the right term, but this is the english of it. > > > > I believe with SQL Server that you can use a > comma-delimited field as the > > target of an IN function as below. It's been a while since > I've used code > > like this, so I could be remembering wrong. > > > > WHERE #session.variable# IN (table.column) > > > > > > > > -Justin Scott > > > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231556 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query finding data IN
The IN operator is used the other way around, but won't help in this scenario...if the possible values really are 1-9, then you could use charindex...otherwise you might look at using patindex. On 2/6/06, Justin D. Scott <[EMAIL PROTECTED]> wrote: > > Where table.sites HAS session.variable I know > > has isn't the right term, but this is the english of it. > > I believe with SQL Server that you can use a comma-delimited field as the > target of an IN function as below. It's been a while since I've used code > like this, so I could be remembering wrong. > > WHERE #session.variable# IN (table.column) > > > > -Justin Scott > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231555 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query finding data IN
> Where table.sites HAS session.variable I know > has isn't the right term, but this is the english of it. I believe with SQL Server that you can use a comma-delimited field as the target of an IN function as below. It's been a while since I've used code like this, so I could be remembering wrong. WHERE #session.variable# IN (table.column) -Justin Scott ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231553 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query finding data IN
If you don't want to change the DB design you could try: SELECT * FROM YourTable WHERE YourColumn LIKE '%,#YourVariable#,%' OR YourColumn LIKE '#YourVariable#,%' OR YourColumn LIKE '%,#YourVariable#' Given a values like: 1,2,3,7,4,5,6 7,1,2,3,4,5,6 1,2,3,4,5,6,7 Looking for 7 it should match all three. Which seems like overkill, but looks like it'd work. Now I'm sure there might be a better way to do this. Personally I'd change the table design. Adrian -Original Message- From: Eric J. Hoffman [mailto:[EMAIL PROTECTED] Sent: 07 February 2006 02:01 To: CF-Talk Subject: Query finding data IN Have a query here I am not sure how to find the answer Have a field in the database with a comma list of items...and want it to only pull a record based upon a session variable that is a single integer. So how do I say give me records where database field(comma delimited) contains session variable? example: Where table.sites HAS session.variable I know has isn't the right term, but this is the english of it. Thanks. Eric J. Hoffman Managing Partner 2081 Industrial Blvd StillwaterMN55082 mail: [EMAIL PROTECTED] www: www.ejhassociates.com tel: 651.207.1526 fax: 651.207.1536 mob: 651.245.2717 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231549 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
