RE: SQL Question - Could CF make this easier?
Or, if possible, add a field to the vct file for include/exclude... ô¿ô Tony -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 11:37 AM To: CF-Talk Subject: SOT: SQL Question - Could CF make this easier? I am creating a drop-down list of items, but I want to keep several items from appearing in the drop-down. Is there a more efficient way to accomplish this (there will be several more exclusions): SELECT DISTINCT Description FROMVCT ORDER BY Description WHERE description != 'ANISE' AND description != 'APPLES' AND description != 'ASPARAGUS' AND description != 'BEETS' AND description != 'BROCCOLI' -- Jillian ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question - Could CF make this easier?
Jillian,
Another thing you may run across is the situation where you don't know
what's going to be in that list because it's being generated by another
query. In that case, the trick is to use a sub-query like so:
SELECT DISTINCT Description
FROMVCT
WHERE description NOT IN (SELECT something FROM SomethingElse)
ORDER BY Description
chris
>-Original Message-
>From: Bryan Love [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, June 11, 2003 11:42 AM
>To: CF-Talk
>Subject: RE: SQL Question - Could CF make this easier?
>
>sure, the IN and NOT IN clauses are much faster than using multiple ANDs,
>but don't transfer the load to CF - allow the DB to do what it's best at...
>
>
>SELECT DISTINCT Description
>FROM VCT
>WHERE description NOT IN ('ANISE','APPLES','ASPARAGUS','BEETS','BROCCOLI')
>ORDER BY Description
>
>
>+---+
>Bryan Love
> Database Analyst
> Macromedia Certified Professional
> Internet Application Developer
>TeleCommunication Systems
>[EMAIL PROTECTED]
>+---+
>
>"...'If there must be trouble, let it be in my day, that my child may have
>peace'..."
> - Thomas Paine, The American Crisis
>
>"Let's Roll"
> - Todd Beamer, Flight 93
>
>
>
>-Original Message-
>From: Jillian Carroll [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, June 11, 2003 11:37 AM
>To: CF-Talk
>Subject: SOT: SQL Question - Could CF make this easier?
>
>
>I am creating a drop-down list of items, but I want to keep several items
>from appearing in the drop-down.
>
>Is there a more efficient way to accomplish this (there will be several
>more
>exclusions):
>
>
>SELECT DISTINCT
> Description
>FROM VCT
>ORDER BY
> Description
>WHERE description != 'ANISE'
> AND description != 'APPLES'
> AND description != 'ASPARAGUS'
> AND description != 'BEETS'
> AND description != 'BROCCOLI'
>
>
>--
>Jillian
>
>
>
>
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community.
http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Question - Could CF make this easier?
You could create a SPROC to do it like this.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE getDescriptions()
AS
SET NOCOUNT ON
SELECT DISTINCT
description
FROM vct
WHERE description
NOT IN (
'ANISE', 'APPLES', 'ASPARAGUS', 'BEETS', 'BROCCOLI'
)
ORDER BY
description
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
- Original Message -
From: "Bryan Love" <[EMAIL PROTECTED]>
sure, the IN and NOT IN clauses are much faster than using multiple ANDs,
but don't transfer the load to CF - allow the DB to do what it's best at...
SELECT DISTINCT Description
FROM VCT
WHERE description NOT IN ('ANISE','APPLES','ASPARAGUS','BEETS','BROCCOLI')
ORDER BY Description
+---+
Bryan Love
Database Analyst
Macromedia Certified Professional
Internet Application Developer
TeleCommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
"Let's Roll"
- Todd Beamer, Flight 93
-Original Message-
From: Jillian Carroll [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 11, 2003 11:37 AM
To: CF-Talk
Subject: SOT: SQL Question - Could CF make this easier?
I am creating a drop-down list of items, but I want to keep several items
from appearing in the drop-down.
Is there a more efficient way to accomplish this (there will be several more
exclusions):
SELECT DISTINCT
Description
FROM VCT
ORDER BY
Description
WHERE description != 'ANISE'
AND description != 'APPLES'
AND description != 'ASPARAGUS'
AND description != 'BEETS'
AND description != 'BROCCOLI'
--
Jillian
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Question - Could CF make this easier?
sure, the IN and NOT IN clauses are much faster than using multiple ANDs,
but don't transfer the load to CF - allow the DB to do what it's best at...
SELECT DISTINCT Description
FROMVCT
WHERE description NOT IN ('ANISE','APPLES','ASPARAGUS','BEETS','BROCCOLI')
ORDER BY Description
+---+
Bryan Love
Database Analyst
Macromedia Certified Professional
Internet Application Developer
TeleCommunication Systems
[EMAIL PROTECTED]
+---+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
"Let's Roll"
- Todd Beamer, Flight 93
-Original Message-
From: Jillian Carroll [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 11, 2003 11:37 AM
To: CF-Talk
Subject: SOT: SQL Question - Could CF make this easier?
I am creating a drop-down list of items, but I want to keep several items
from appearing in the drop-down.
Is there a more efficient way to accomplish this (there will be several more
exclusions):
SELECT DISTINCT
Description
FROMVCT
ORDER BY
Description
WHERE description != 'ANISE'
AND description != 'APPLES'
AND description != 'ASPARAGUS'
AND description != 'BEETS'
AND description != 'BROCCOLI'
--
Jillian
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
