Re: Excluding IDs in queries
Use NOT IN WHERE featured = No AND ID NOT IN (#ListofIDs#) - Original Message - From: "W Luke" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Sunday, April 01, 2001 9:48 AM Subject: Excluding IDs in queries Hello. I'm trying to exclude an ID from the WHERE in a query, by looping through a list, but it's causing a lot of headaches. The list contains delimited list of IDS (120,145,987,564 etc) cfquery name="users_adverts" datasource="localads" SELECT ID,email,subject,paid FROM advert_details WHERE featured = No AND ( cfloop index="i" list="#client.troll#" OR ID #i# /cfloop) /cfquery I know the above is glaringly wrong, but I've tried everything - is it the positioning of ANDs and ORs? Any advice would be muuuch appreciated. Will ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Excluding IDs in queries
Use the IN operator cfquery name="users_adverts" datasource="localads" SELECT ID,email,subject,paid FROM advert_details WHERE featured IN (120,145,987,564) /cfquery |-Original Message- |From: W Luke [mailto:[EMAIL PROTECTED]] |Sent: Sunday, April 01, 2001 12:48 PM |To: CF-Talk |Subject: Excluding IDs in queries | | |Hello. | |I'm trying to exclude an ID from the WHERE in a query, by |looping through a |list, but it's causing a lot of headaches. The list contains |delimited list |of IDS (120,145,987,564 etc) | | cfquery name="users_adverts" datasource="localads" |SELECT ID,email,subject,paid FROM advert_details |WHERE featured = No |AND ( |cfloop index="i" list="#client.troll#" |OR ID #i# |/cfloop) |/cfquery | |I know the above is glaringly wrong, but I've tried everything |- is it the |positioning of ANDs and ORs? | |Any advice would be muuuch appreciated. | |Will | | | ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Excluding IDs in queries
Thanks, working now..:/ Will - Original Message - From: ""Sean Renet"" [EMAIL PROTECTED] Newsgroups: dotcom.lists.cftalk Sent: Sunday, April 01, 2001 6:05 PM Subject: Re: Excluding IDs in queries Use NOT IN WHERE featured = No AND ID NOT IN (#ListofIDs#) - Original Message - From: "W Luke" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Sunday, April 01, 2001 9:48 AM Subject: Excluding IDs in queries Hello. I'm trying to exclude an ID from the WHERE in a query, by looping through a list, but it's causing a lot of headaches. The list contains delimited list of IDS (120,145,987,564 etc) cfquery name="users_adverts" datasource="localads" SELECT ID,email,subject,paid FROM advert_details WHERE featured = No AND ( cfloop index="i" list="#client.troll#" OR ID #i# /cfloop) /cfquery I know the above is glaringly wrong, but I've tried everything - is it the positioning of ANDs and ORs? Any advice would be muuuch appreciated. Will ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Excluding IDs in queries
AND ID NOT IN #CLIENT.TROLL# (The RDBMS will translate IN into multiple OR statements and NOT IN into multiple AND statements.) -David On Sunday, April 01, 2001 9:48 AM, W Luke [SMTP:[EMAIL PROTECTED]] wrote: Hello. I'm trying to exclude an ID from the WHERE in a query, by looping through a list, but it's causing a lot of headaches. The list contains delimited list of IDS (120,145,987,564 etc) cfquery name="users_adverts" datasource="localads" SELECT ID,email,subject,paid FROM advert_details WHERE featured = No AND ( cfloop index="i" list="#client.troll#" OR ID #i# /cfloop) /cfquery I know the above is glaringly wrong, but I've tried everything - is it the positioning of ANDs and ORs? Any advice would be muuuch appreciated. Will ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Excluding IDs in queries
The below should do the trick - I didn't have time to check it but give it a shot. cfquery name="users_adverts" datasource="localads" SELECT ID,email,subject,paid FROM advert_details WHERE featured = No AND ( cfloop index="i" list="#client.troll#" ID #i# OR /cfloop 1=2) /cfquery -Original Message- From: W Luke [mailto:[EMAIL PROTECTED]] Sent: Sunday, April 01, 2001 9:48 AM To: CF-Talk Subject: Excluding IDs in queries Hello. I'm trying to exclude an ID from the WHERE in a query, by looping through a list, but it's causing a lot of headaches. The list contains delimited list of IDS (120,145,987,564 etc) cfquery name="users_adverts" datasource="localads" SELECT ID,email,subject,paid FROM advert_details WHERE featured = No AND ( cfloop index="i" list="#client.troll#" OR ID #i# /cfloop) /cfquery I know the above is glaringly wrong, but I've tried everything - is it the positioning of ANDs and ORs? Any advice would be muuuch appreciated. Will ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Excluding IDs in queries
In certain situations, I have run into trouble using IN with lists of values
... most notably when also using sub-queries. If you run accross the same
problem, also try:
WHERE LOCATE('#Client.Troll#', ID, 1) = 0
Todd Ashworth
Web Application Developer
Network Administrator
Saber Corporation
314 Oakland Ave.
Rock Hill, SC 29730
(803) 327-0137 [111] (p)
(803) 328-2868 (f)
- Original Message -
From: "David Shadovitz" [EMAIL PROTECTED]
To: "CF-Talk" [EMAIL PROTECTED]
Sent: Sunday, April 01, 2001 1:20 PM
Subject: RE: Excluding IDs in queries
AND ID NOT IN #CLIENT.TROLL#
(The RDBMS will translate IN into multiple OR statements and NOT IN into
multiple AND statements.)
-David
On Sunday, April 01, 2001 9:48 AM, W Luke [SMTP:[EMAIL PROTECTED]]
wrote:
Hello.
I'm trying to exclude an ID from the WHERE in a query, by looping
through a
list, but it's causing a lot of headaches. The list contains delimited
list
of IDS (120,145,987,564 etc)
cfquery name="users_adverts" datasource="localads"
SELECT ID,email,subject,paid FROM advert_details
WHERE featured = No
AND (
cfloop index="i" list="#client.troll#"
OR ID #i#
/cfloop)
/cfquery
I know the above is glaringly wrong, but I've tried everything - is it
the
positioning of ANDs and ORs?
Any advice would be muuuch appreciated.
Will
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
