In his example, delaersmanufacturers is a column name, not a CF variable. And in that case, the IN is used to say find any records whose given COLUMN has values IN a given list of comma-separated values. It looks like what Branden wants to do is find a record where a given value is one of several within a given COLUMN. That's just not solvable with an IN clause (at least, I don't think so. Maybe someone can prove me wrong! Especially if I've misunderstood what he's asking for).
/charlie -----Original Message----- From: Cathy and Dave [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 22, 2001 11:03 PM To: SQL Subject: Re: HELP!!! can you use WHERE manuid IN '#dealersmanufacturers#' ? ----- Original Message ----- From: webmaster <[EMAIL PROTECTED]> To: SQL <[EMAIL PROTECTED]> Sent: Friday, December 21, 2001 11:38 PM Subject: HELP!!! > This is a multi-part message in MIME format. > > ------=_NextPart_000_0046_01C18A70.411749A0 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > Is there anyone who can help me figure out this problem? > > This is what I already have below. Essentially I am trying to compare a = > single value "manuId" to a comma delimited list of values which is the = > field dealersmanufacturers. Can anyone shed some light on this sql = > dilema? > > Thanks, Branden > > <cfquery name=3D"dealersMainBody" datasource=3D"#request.maindsn#" = > dbtype=3D"#request.dsntype#"> > SELECT dealersId, dealersName, dealersLink, dealersDescription, = > dealersCategoryId, dealersState, dealersDateCreated, = > dealersDateLastModified, dealersOnOff > FROM tbldealer > WHERE dealersCategoryId =3D #variables.DivisionId# and dealersOnOff =3D = > 1=20 > <!--- 12/18/01=20 > Look for three unique strings, if string is the=20 > (1) first in the list > (2) last in the list > (3) middle of the list > ---> > <cfif isdefined("attributes.manuid")> > and=20 > ( > dealersmanufacturers like '#left(attributes.manuId,lenam)#,%' > or > dealersmanufacturers like '%,#right(attributes.manuId,lenam)#' > or > dealersmanufacturers like '%,#attributes.manuId#,%'=20 > ) > </cfif> > <!--- Bob END ---> > ORDER BY dealersName > </cfquery> > > ------=_NextPart_000_0046_01C18A70.411749A0 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META http-equiv=3DContent-Type content=3D"text/html; = > charset=3Diso-8859-1"> > <META content=3D"MSHTML 5.50.4207.2601" name=3DGENERATOR> > <STYLE></STYLE> > </HEAD> > <BODY bgColor=3D#ffffff> > <DIV><FONT face=3DArial size=3D2> > <DIV><FONT face=3DArial size=3D2>Is there anyone who can help me figure = > out this=20 > problem?</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>This is what I already have = > below. =20 > Essentially I am trying to compare a single value "manuId" to a comma = > delimited=20 > list of values which is the field dealersmanufacturers. Can anyone = > shed=20 > some light on this sql dilema?</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>Thanks, Branden</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2><cfquery name=3D"dealersMainBody"=20 > datasource=3D"#request.maindsn#" = > dbtype=3D"#request.dsntype#"><BR>SELECT=20 > dealersId, dealersName, dealersLink, dealersDescription, = > dealersCategoryId,=20 > dealersState, dealersDateCreated, dealersDateLastModified, = > dealersOnOff<BR>FROM=20 > tbldealer<BR>WHERE dealersCategoryId =3D #variables.DivisionId# and = > dealersOnOff =3D=20 > 1 <BR><!--- 12/18/01 <BR> Look for three = > unique=20 > strings, if string is the <BR> (1) first in the=20 > list<BR> (2) last in the list<BR> (3) middle of = > the=20 > list<BR>---><BR><cfif = > isdefined("attributes.manuid")><BR> and=20 > <BR> (<BR> dealersmanufacturers like=20 > '#left(attributes.manuId,lenam)#,%'<BR> or<BR> dealersman= > ufacturers=20 > like=20 > '%,#right(attributes.manuId,lenam)#'<BR> or<BR> dealersma= > nufacturers=20 > like = > '%,#attributes.manuId#,%' <BR> )<BR></cfif><BR><!--- = > Bob=20 > END ---><BR>ORDER BY=20 > dealersName<BR></cfquery></FONT></DIV></FONT></DIV></BODY></HTML> > > ------=_NextPart_000_0046_01C18A70.411749A0-- > > Archives: http://www.mail-archive.com/[email protected]/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
