Les,
Great! Yep, didn't think of the situation where the list could be only one
item.
Hey, you should also add parenthesis around the OR clauses for clarity sake.
Here:
SELECT distinct lname, fname, artist_id
FROM photos, photographers
WHERE (photos.theme_id = '#themes.id#'
OR photos.theme_id LIKE '%,#themes.id#,%'
OR photos.theme_id LIKE '#themes.id#,%'
OR photos.theme_id LIKE '%,#themes.id#')
AND photographers.id = photos.artist_id
order by lname
-----Original Message-----
From: Les Irvin [mailto:[EMAIL PROTECTED]
Sent: Saturday, January 27, 2007 12:58 PM
To: CF-Talk
Subject: Re: Querying a delimited text field
With one small tweak it works perfect. (I failed to mention that sometimes
there is just a single integer in the field).
SELECT distinct lname, fname, artist_id
FROM photos, photographers
WHERE photos.theme_id = '#themes.id#'
OR photos.theme_id LIKE '%,#themes.id#,%'
OR photos.theme_id LIKE '#themes.id#,%'
OR photos.theme_id LIKE '%,#themes.id#'
AND photographers.id = photos.artist_id
order by lname
Thanks for all your help guys! I think this list is the only reason I still
have a job. :-) Les
On 1/27/07, Dave Phillips <[EMAIL PROTECTED]> wrote:
>
> Les,
>
> Try this:
>
> <CFQUERY NAME="names" DATASOURCE="#DB_redwood#">
> SELECT distinct lname, fname, artist_id
> FROM photos, photographers
> WHERE photographers.id = photos.artist_id
> AND photos.theme_id LIKE '%,#themes.id#,%'
> OR photos.theme_id LIKE '#themes.id#,%'
> OR photos.theme_id LIKE '%,#themes.id#'
> order by lname
> </CFQUERY>
>
> Let me know if it works.
>
> Dave
> -----Original Message-----
> From: Les Irvin [mailto:[EMAIL PROTECTED]
> Sent: Saturday, January 27, 2007 12:26 PM
> To: CF-Talk
> Subject: Re: Querying a delimited text field
>
>
> Yes, querying an Access DB to find it.
>
> This is my current (lousy and failing) code for attempting it:
>
> <CFQUERY NAME="names" DATASOURCE="#DB_redwood#">
> SELECT distinct lname, fname, artist_id
> FROM photos, photographers
> WHERE #find("themes.id",[photos.theme_id])# <> 0
> and photographers.id = photos.artist_id
> order by lname
> </CFQUERY>
>
> photos.theme_id is the delimited field.
>
> On 1/27/07, Dave Phillips <[EMAIL PROTECTED]> wrote:
> >
> > Les,
> >
> > Are you writing a query to find this in a database? If so, what db
> > are you using?
> >
> > Or, are you just trying to use CF code to find it in text?
> >
> > Dave
> >
> > -----Original Message-----
> > From: Les Irvin [mailto:[EMAIL PROTECTED]
> > Sent: Saturday, January 27, 2007 12:11 PM
> > To: CF-Talk
> > Subject: Querying a delimited text field
> >
> >
> > Novice question here.
> >
> > I need to write a query to find a single integer in a field of
> > delimited integers, say "23,25,27,29". How is this done in Cold
> > Fusion? FindOneOf? FindNoCase? Can anyone advise?
> >
> > Thanks in advance for any help,
> > Les
> >
> >
> >
> >
> >
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267849
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
