SOT: SQL question

[Chris Lott]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

In a table something like this:

userid, course
john ethics
john english
mary english
mary math
mary bio

I need to find 

a) which user has taken the most courses in total
b) which course was taken the most often

Ideas?

c

-BEGIN PGP SIGNATURE-
Version: 6.5.8ckt http://irfaiad.virtualave.net/
Comment: PGP Signed for message verification and/or encryption
Comment: KeyID: 0xD68B61E851046CFD

iQA/AwUBOd0ksNaLYehRBGz9EQJjVQCfUA600RImF5Y7NRoha4BGnHzrV/QAoKWx
9BIcf5UEne847/fTRg/Ryv/y
=CpJ4
-END PGP SIGNATURE-


--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

SOT: SQL question

[Cutter (CF-Talk)]

How do I get the ID of the last record in a table (mySQL db)?

Cutter
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

SOT: SQL question

[Cutter (CF-Talk)]

Ok, I'm back to this. Have an ISP who is using the outdated MySQL 3.23, 
which does not support Union statements in it's syntax. I need to 
combine the data of two separate but similar statements so that I may 
sort on a certain field. These two tables are not Joined by any keys, 
though it could be perceived that way (the pk of each is auto-numbered 
so there are many matching values, don't want to eliminate anything in 
my results). Any ideas?

Cutter
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

SOT: SQL QUESTION

[Tangorre, Michael]

SQL SERVER 2000
CFMX 6.1

I have three tables called: OPPORTUNITY, OPPORTUNITY_TEXT, TEXT_TYPE

OPPORTUNITY_TEXT has two columns making the PK: TEXT_TYPE_ID and
OPPORTUNITY_ID. In addition there is a column called OPPORTUNITY_TEXT_DATA
which holds the actual text I am after [varchar(6000)]. What I am trying to
do is pull back two specific records if they exist but as one record. Let me
elaborate

Currently, I can say the following:

SELECT
	OPPORTUNITY_ID AS C1,
	TEXT_TYPE_ID AS C2,
	OPPORTUNITY_TEXT_DATA AS C3
FROM
	OPPORTUNITY_TEXT
WHERE
	OPPORTUNITY_ID = 1
	AND
	(TEXT_TYPE_ID = 12 OR TEXT_TYPE_ID = 13)

Which returns two records as expected.

-
C1	|	C2	|	C3
--|---|--
1	|	12	|	A
-
1	|	13	|	B
-

What I would like to do is join on itself and alias the columns so I can get
the output to be something like so that I can avoid all the conditional
logic in my page that would be needed to output the correct value in the
correct form field:


Alias1	|	Alias2
|---
A		|	B 


Does that make sense? Am I missing the boat or something here. Open for
suggestions and assistance.

Thanks!

Mike
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

SOT: SQL question

[Cutter (CF-Talk)]

I have four (4) tables. One table holds IDs that reference info in the 
other three (3) tables by ID. I am trying to pull the information for an 
item, and pull the info specific to that item from the three tables. 
What's wrong with this picture?

select		i.intBoutItemID as ID,
		c.txtBoutCat as Cat,
		s.txtBoutSubCat as SubCat,
		d.txtDesignName as Name,
		i.blActive as Active
from		tblBoutItem i,
		tblBoutCat c,
		tblBoutSubCat s,
		tblBoutDesign d
where		c.intBoutCatID = i.intBoutCatID
and		s.intBoutSubCatID = i.intBoutSubCatID
and		d.intBoutDesignID = i.intBoutDesignID
Order by	d.txtDesignName
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

SOT: SQL Question

[Tangorre, Michael]

Given the following:

 
SELECT 
 R.TEAMING_AGREEMENT_ID,
 R.ROUTE_GROUP_ID,
 B.BUSINESS_NAME,
 T.OPPORTUNITY_ID,
 D.DOCUMENT_NAME,
 D.DOCUMENT_EXTENSION
FROM
 TEAMING_AGREEMENT_ROUTING R
 INNER JOIN TEAMING_AGREEMENT T
  ON (R.TEAMING_AGREEMENT_ID = T.TEAMING_AGREEMENT_ID)
 INNER JOIN COMMUNITY_BUSINESS B
  ON (T.COMMUNITY_BUSINESS_ID = B.COMMUNITY_BUSINESS_ID)
 INNER JOIN TEAMING_AGREEMENT_TYPE TAT
  ON (T.TEAMING_AGREEMENT_TYPE_ID = TAT.TEAMING_AGREEMENT_TYPE_ID)
 INNER JOIN TEAMING_AGREEMENT_DOCUMENT D
  ON (T.TEAMING_AGREEMENT_ID = D.TEAMING_AGREEMENT_ID)
WHERE
 T.OPPORTUNITY_ID = 33
GROUP BY
 B.BUSINESS_NAME,
 R.TEAMING_AGREEMENT_ID,
 R.ROUTE_GROUP_ID,
 T.OPPORTUNITY_ID,
 D.DOCUMENT_NAME,
 D.DOCUMENT_EXTENSION

 
If I need to get two of documents from the TEAMING_AGREEMENT_DOCUMENT
table (different types: DOC_TYPE = 1 and DOC_TYPE =2), how can I do this
and get the desired number of records returned. If I remove the joined
in TEAMING_AGREEMENT_DOCUMENT table I return 5 records, which is
correct, but when I try and get the two documents associated I return 10
records. Is there anyway to qualify the selection of the documents? I
hope this makes sense grouping? sub query?

 
Thanks!

 
Mike
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]
 [Donations and Support]

SOT: SQL Question

[Brad Wood]

This one's been bugging me for a day now.  I don't think this is
possible, but before I gave up I thought I would ask.  (Yes Rick, I
googled it first)

As usual, the real scenario is much more complicated, but this is a
simple example that shows the concept. 

Let's say I had a table with multiple products per order and the date
they were ordered:

order_num   product_namedatetime_created
1   apples  9/1/2007
1   oranges 9/10/2007
1   bananas 9/20/2007
2   apples  9/5/2007
2   pears   9/15/2007
2   kiwi9/25/2007

Is it possible with a SINGLE select statement to simply get a distinct
list of orders represented with the LAST product ordered like so:

order_num   product_namedatetime_created
1   bananas 9/20/2007
2   kiwi9/25/2007

The knee jerk reaction is to group by order_num, and then use the max
aggregate on datetime_created.   That's fine, but then you can't get the
corresponding product_name.  To get the product name in the select list
you have to add it in the group by which then itemizes all the products
and you no longer have a distinct list of orders.

Using a derived table doesn't even help.  I can't do "top 1" with an
"order by datetime_ordered desc" because I am reporting across multiple
orders.

I am on MS SQL Server 2005.  The only ways I can find to do this are:

1) Cursor or while loop over orders getting last product row by
agonizing row

2) Create function to return last product on the order, and "cross
apply" it.  The function still has to run RBAR for every order though.

3) Initial select into a temp table with max(datetime_created).  Update
temp table in a second pass with product_name joining on
datetime_created.  I don't like joining to a date because it guaranteed
unique.

Ideas?

Thanks.

~Brad

~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289743
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

SOT: SQL Question

[Dave Phillips]

Hey, I'm wondering if anyone would know T-SQL I could use to accomplish the
following (I don't have the luxury of writing an SQL function for this,
unfortunately):
 
I have a column called 'path' in a database (Oracle) that has the following
types of values in it:
 
Example 1: c:\foldername\documentname.ext (standard format)
Example 2: c:\foldername\subfolder\my.document.name.ext(multiple
periods)
Example 3: c:\foldername\documentname (no extension)
 
What I want to do is be able to extract the extension from the path in a
select statement.  I know I can do this easily in CF code, I'm trying to
avoid looping through a large query and doing it if at all possible.  Here's
what I have so far, but it's too simple and doesn't work on example 2 above:
 
select decode(inStr(Path,'.'),0,'',subStr(path,inStr(path,'.')+1,3)) from
document
 
I suppose I need to find a way to do something like a 'rightInStr()' or
something like that.  A way to get the position of the LAST period.  Or if
someone has some other idea of how I can do it in T-SQL, maybe using some
CASE statements or something?  I'm not great with CASE and SQL.
 
Please, if anyone can help, I would greatly appreciate it!
 
Sincerely,
 
Dave Phillips
WebTech Staffing, LLC
 



~|
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:269408
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

SOT sql question

[Mark Fuqua]

I think I need to be using 'IN' within my WHERE clause but I can't seem to
get it to work.  I have a column with a comma delimited list.  This is the
latest attempt and it craps out too.

 

SELECT..

 

...WHERE JobFileJob = #session.jobId# AND  '#session.UserRole#' IN
(JobFileAccessLevel)

 

JobFileAccessLevel is a field in the database table that contains a comma
delimited list of access levels.

 

Maybe I'm barking up the wrong tree entirely.  What I'm trying to do is.

 

..WHERE jobFileJob=#session.jobId# and jobFileAccessLevel CONTAINS
'#session.UserRole#'

 

But I don't think 'contains' exists.

 

Thanks,

 

Mark




~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296263
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SOT: SQL question

[Jochem van Dieten]

Cutter (CF-Talk) wrote:

> How do I get the ID of the last record in a table (mySQL db)?

Define 'last'.

Jochem

-- 
I don't get it
immigrants don't work
and steal our jobs
 - Loesje
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SOT: SQL question

[Cutter (CF-Talk)]

'last' = the last/most recent/highest autonumbered ID in the primary key 
of the table.

Cutter

Jochem van Dieten wrote:

> Cutter (CF-Talk) wrote:
> 
>  > How do I get the ID of the last record in a table (mySQL db)?
> 
> Define 'last'.
> 
> Jochem
> 
> -- 
> I don't get it
> immigrants don't work
> and steal our jobs
>  - Loesje
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SOT: SQL question

[Jochem van Dieten]

Cutter (CF-Talk) wrote:

> 'last' = the last/most recent/highest autonumbered ID in the primary key 
> of the table.

http://www.mysql.com/doc/en/Getting_unique_ID.html

Jochem

-- 
I don't get it
immigrants don't work
and steal our jobs
 - Loesje
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SOT: SQL question

[Ubqtous]

Cutter,

On 2/23/2004 at 14:07, you wrote:

CCT> Ok, I'm back to this. Have an ISP who is using the outdated MySQL
CCT> 3.23, which does not support Union statements in it's syntax. I
CCT> need to combine the data of two separate but similar statements
CCT> so that I may sort on a certain field. These two tables are not
CCT> Joined by any keys, though it could be perceived that way (the pk
CCT> of each is auto-numbered so there are many matching values, don't
CCT> want to eliminate anything in my results). Any ideas?

Query of Queries?

http://livedocs.macromedia.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets.htm

~ Ubqtous ~
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SOT: SQL question

[Cutter (CF-Talk)]

I've tried a query of query situation, but it is not returning all 
records from both tables, and I believe it may be because both tables 
have a key value that match. Here are my queries and the end results:

**qHoliday**
select		h.intHolidayPlusID 		as ID,
			h.txtHolidayPlusTitle 	as Title,
			h.dtStartDate 			as SDate,
			h.dtEndDate 			as EDate
from		tblholidayplus h
where		h.dtStartDate >= #createodbcdate(variables.originalurldate)#
or	 		#createodbcdate(url.date)# <= h.dtEndDate
order by	h.dtStartDate
limit 		7

**Results of qHoliday**
   EDATE ID SDATE TITLE
1 {ts '2004-02-24 00:00:00'} 2 {ts '2004-02-24 00:00:00'} Fat Tuesday
2 {ts '2004-02-17 00:00:00'} 3 {ts '2004-03-17 00:00:00'} St Patricks Day
3 {ts '2004-04-01 00:00:00'} 4 {ts '2004-04-01 00:00:00'} April Fools Day

**qHoliday2**
select		s.intSpEventsID			as ID,
			s.txtSpEventsTitle		as Title,
			d.dtDatesas SDate,
			s.dtEndTimeas EDate
from		tblspevents s,
			tbleventdates d
where		s.intEventDatesID = d.intEventDatesID
and			d.dtDates >= #createodbcdate(variables.originalurldate)#
order by	d.dtDates
limit 		7

**Results of qHoliday2**
   EDATE ID SDATE TITLE
1 {ts '1970-01-01 02:00:00'} 3 {ts '2004-02-24 00:00:00'} Mardi Gras Party

**qEventList QofQ**
select 		*
from		qHoliday,
			qHoliday2
order by	SDate

**Results of qEventList**
   EDATE EDATE ID ID SDATE SDATE TITLE TITLE
1 {ts '2004-02-24 00:00:00'} {ts '2004-02-24 00:00:00'} 2 2 {ts 
'2004-02-24 00:00:00'} {ts '2004-02-24 00:00:00'} Fat Tuesday Fat Tuesday
2 {ts '2004-02-17 00:00:00'} {ts '2004-02-17 00:00:00'} 3 3 {ts 
'2004-03-17 00:00:00'} {ts '2004-03-17 00:00:00'} St Patricks Day St 
Patricks Day
3 {ts '2004-04-01 00:00:00'} {ts '2004-04-01 00:00:00'} 4 4 {ts 
'2004-04-01 00:00:00'} {ts '2004-04-01 00:00:00'} April Fools Day April 
Fools Day

Ubqtous wrote:

> Cutter,
> 
> On 2/23/2004 at 14:07, you wrote:
> 
> CCT> Ok, I'm back to this. Have an ISP who is using the outdated MySQL
> CCT> 3.23, which does not support Union statements in it's syntax. I
> CCT> need to combine the data of two separate but similar statements
> CCT> so that I may sort on a certain field. These two tables are not
> CCT> Joined by any keys, though it could be perceived that way (the pk
> CCT> of each is auto-numbered so there are many matching values, don't
> CCT> want to eliminate anything in my results). Any ideas?
> 
> Query of Queries?
> 
> http://livedocs.macromedia.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets.htm
> 
> ~ Ubqtous ~
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SOT: SQL question

[Ubqtous]

Cutter,

On 2/23/2004 at 14:50, you wrote:

CCT> **qEventList QofQ**
CCT> select  *
CCT> fromqHoliday,
CCT> qHoliday2
CCT> order bySDate

maybe try this in your QoQ:

select * from qHoliday
union
select * from qHoliday2
order by SDate

~ Ubqtous ~
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SOT: SQL question

[Cutter (CF-Talk)]

As listed in the original post, my ISP is using MySQL 3.23 (which has no 
support for union)...

Cutter

Ubqtous wrote:

> Cutter,
> 
> On 2/23/2004 at 14:50, you wrote:
> 
> CCT> **qEventList QofQ**
> CCT> select  *
> CCT> fromqHoliday,
> CCT> qHoliday2
> CCT> order bySDate
> 
> maybe try this in your QoQ:
> 
> select * from qHoliday
> union
> select * from qHoliday2
> order by SDate
> 
> ~ Ubqtous ~
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SOT: SQL question

[Mark A. Kruger - CFG]

Yes but Query of queries DOES support the union key word.

-Mark

  -Original Message-
  From: Cutter (CF-Talk) [mailto:[EMAIL PROTECTED]
  Sent: Monday, February 23, 2004 2:01 PM
  To: CF-Talk
  Subject: Re: SOT: SQL question

  As listed in the original post, my ISP is using MySQL 3.23 (which has no 
  support for union)...

  Cutter

  Ubqtous wrote:

  > Cutter,
  > 
  > On 2/23/2004 at 14:50, you wrote:
  > 
  > CCT> **qEventList QofQ**
  > CCT> select  *
  > CCT> fromqHoliday,
  > CCT> qHoliday2
  > CCT> order bySDate
  > 
  > maybe try this in your QoQ:
  > 
  > select * from qHoliday
  > union
  > select * from qHoliday2
  > order by SDate
  > 
  > ~ Ubqtous ~
  >
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SOT: SQL question

[Ubqtous]

Cutter,

On 2/23/2004 at 15:01, you wrote:

CCT> As listed in the original post, my ISP is using MySQL 3.23 (which
CCT> has no support for union)...

The QoQ doesn't touch the database, rather it's using the record sets
from the queries you run before hand. The query results are still in
server memory when you run the QoQ, which is why MySQL isn't touched.

1. Run qHoliday against MySQL
2. Run qHoliday2 against MySQL
3. Run QoQ with a UNION clause to combine the results of qHoliday &
qHoliday2.

~ Ubqtous ~
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SOT: SQL question

[Jochem van Dieten]

Cutter (CF-Talk) wrote:
> I have four (4) tables. One table holds IDs that reference info in the 
> other three (3) tables by ID. I am trying to pull the information for an 
> item, and pull the info specific to that item from the three tables. 
> What's wrong with this picture?
> 
> select		i.intBoutItemID as ID,
> 		c.txtBoutCat as Cat,
> 		s.txtBoutSubCat as SubCat,
> 		d.txtDesignName as Name,
> 		i.blActive as Active
> from		tblBoutItem i,
> 		tblBoutCat c,
> 		tblBoutSubCat s,
> 		tblBoutDesign d
> where		c.intBoutCatID = i.intBoutCatID
> and		s.intBoutSubCatID = i.intBoutSubCatID
> and		d.intBoutDesignID = i.intBoutDesignID
> Order by	d.txtDesignName

Nothing: http://developer.mimer.com/validator/parser99/index.tml

So why don't you tell us what is wrong?

Jochem

-- 
When you don't want to be surprised by the revolution
organize one yourself
 - Loesje
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SOT: SQL Question

[Brad Wood]

> why do you have to use a single select statement? Thats pretty 
> limiting there..

1) To see if it can be done
2) It seemed that it may perform best to make a single pass at the table
as opposed to two passes.  (That might not be true-- it was just a
thought)

The query below works, but does not return a distinct list of orders.
If multiple products were added at the same time, I get all of them.
I'm really looking for a top 1 sort of deal I guess.

~Brad

-Original Message-
From: Greg Morphis [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 28, 2007 11:47 AM
To: CF-Talk
Subject: Re: SOT: SQL Question

select t.order_num, t.product_name, t.datetime_created
from test t,
(
select order_num, max(datetime_created) maxdt
  from test
  group by order_num
)  t2
where t2.order_num = t.order_num
and t2.maxdt = t.datetime_created



1 query, but 2 selects = gets what you want

~|
ColdFusion is delivering applications solutions at at top companies 
around the world in government.  Find out how and where now
http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289755
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SOT: SQL Question

[Greg Morphis]

why do you have to use a single select statement? Thats pretty limiting there..

On 9/28/07, Brad Wood <[EMAIL PROTECTED]> wrote:
> This one's been bugging me for a day now.  I don't think this is
> possible, but before I gave up I thought I would ask.  (Yes Rick, I
> googled it first)
>
> As usual, the real scenario is much more complicated, but this is a
> simple example that shows the concept.
>
> Let's say I had a table with multiple products per order and the date
> they were ordered:
>
> order_num   product_namedatetime_created
> 1   apples  9/1/2007
> 1   oranges 9/10/2007
> 1   bananas 9/20/2007
> 2   apples  9/5/2007
> 2   pears   9/15/2007
> 2   kiwi9/25/2007
>
> Is it possible with a SINGLE select statement to simply get a distinct
> list of orders represented with the LAST product ordered like so:
>
> order_num   product_namedatetime_created
> 1   bananas 9/20/2007
> 2   kiwi9/25/2007
>
> The knee jerk reaction is to group by order_num, and then use the max
> aggregate on datetime_created.   That's fine, but then you can't get the
> corresponding product_name.  To get the product name in the select list
> you have to add it in the group by which then itemizes all the products
> and you no longer have a distinct list of orders.
>
> Using a derived table doesn't even help.  I can't do "top 1" with an
> "order by datetime_ordered desc" because I am reporting across multiple
> orders.
>
> I am on MS SQL Server 2005.  The only ways I can find to do this are:
>
> 1) Cursor or while loop over orders getting last product row by
> agonizing row
>
> 2) Create function to return last product on the order, and "cross
> apply" it.  The function still has to run RBAR for every order though.
>
> 3) Initial select into a temp table with max(datetime_created).  Update
> temp table in a second pass with product_name joining on
> datetime_created.  I don't like joining to a date because it guaranteed
> unique.
>
> Ideas?
>
> Thanks.
>
> ~Brad
>
> 

~|
ColdFusion is delivering applications solutions at at top companies 
around the world in government.  Find out how and where now
http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289748
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SOT: SQL Question

[Greg Morphis]

select t.order_num, t.product_name, t.datetime_created
from test t,
(
select order_num, max(datetime_created) maxdt
  from test
  group by order_num
)  t2
where t2.order_num = t.order_num
and t2.maxdt = t.datetime_created



1 query, but 2 selects = gets what you want

On 9/28/07, Greg Morphis <[EMAIL PROTECTED]> wrote:
> why do you have to use a single select statement? Thats pretty limiting 
> there..
>
> On 9/28/07, Brad Wood <[EMAIL PROTECTED]> wrote:
> > This one's been bugging me for a day now.  I don't think this is
> > possible, but before I gave up I thought I would ask.  (Yes Rick, I
> > googled it first)
> >
> > As usual, the real scenario is much more complicated, but this is a
> > simple example that shows the concept.
> >
> > Let's say I had a table with multiple products per order and the date
> > they were ordered:
> >
> > order_num   product_namedatetime_created
> > 1   apples  9/1/2007
> > 1   oranges 9/10/2007
> > 1   bananas 9/20/2007
> > 2   apples  9/5/2007
> > 2   pears   9/15/2007
> > 2   kiwi9/25/2007
> >
> > Is it possible with a SINGLE select statement to simply get a distinct
> > list of orders represented with the LAST product ordered like so:
> >
> > order_num   product_namedatetime_created
> > 1   bananas 9/20/2007
> > 2   kiwi9/25/2007
> >
> > The knee jerk reaction is to group by order_num, and then use the max
> > aggregate on datetime_created.   That's fine, but then you can't get the
> > corresponding product_name.  To get the product name in the select list
> > you have to add it in the group by which then itemizes all the products
> > and you no longer have a distinct list of orders.
> >
> > Using a derived table doesn't even help.  I can't do "top 1" with an
> > "order by datetime_ordered desc" because I am reporting across multiple
> > orders.
> >
> > I am on MS SQL Server 2005.  The only ways I can find to do this are:
> >
> > 1) Cursor or while loop over orders getting last product row by
> > agonizing row
> >
> > 2) Create function to return last product on the order, and "cross
> > apply" it.  The function still has to run RBAR for every order though.
> >
> > 3) Initial select into a temp table with max(datetime_created).  Update
> > temp table in a second pass with product_name joining on
> > datetime_created.  I don't like joining to a date because it guaranteed
> > unique.
> >
> > Ideas?
> >
> > Thanks.
> >
> > ~Brad
> >
> > 

~|
Enterprise web applications, build robust, secure 
scalable apps today - Try it now ColdFusion Today
ColdFusion 8 beta - Build next generation apps

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289751
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SOT: SQL Question

[Kris Jones]

how about something that looks at the right 4 characters, and if the
first of those 4 is a period, then get the right 3. I don't have SQL
Server on this box, but it would be something like:

select case left(right(path,4),1) when '.' then right(path,3) else '' end

Again, don't have it in front of me, but, give it a try.

Cheers,
Kris


> I have a column called 'path' in a database (Oracle) that has the following
> types of values in it:
>
> Example 1: c:\foldername\documentname.ext (standard format)
> Example 2: c:\foldername\subfolder\my.document.name.ext(multiple
> periods)
> Example 3: c:\foldername\documentname (no extension)
>
> What I want to do is be able to extract the extension from the path in a
> select statement.  I know I can do this easily in CF code, I'm trying to
> avoid looping through a large query and doing it if at all possible.  Here's
> what I have so far, but it's too simple and doesn't work on example 2 above:
>
> select decode(inStr(Path,'.'),0,'',subStr(path,inStr(path,'.')+1,3)) from
> document

~|
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:269410
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SOT: SQL Question

[Jim Wright]

Kris Jones wrote:
> select case left(right(path,4),1) when '.' then right(path,3) else '' end
> 

This will work on SQL Server (assuming all of your extensions are three 
characters), and possibly on Oracle(I think it now supports CASE).  In 
your message you wanted a T-SQL solution, but said your database was 
Oracle.  Did you mean to say a PL/SQL or possible just SQL solution? 
T-SQL is the proprietary version of SQL specific to Sybase and SQL Server.

~|
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:269416
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SOT: SQL Question

[Dave Phillips]

Jim,

Yeah, I typoed.  Sorry, worked with SQL Server a lot.  I did mean a PL/SQL 
solution, however, I cannot write a PL/SQL function to utilize, so whatever SQL 
I use, it has to work inside a 

To my knowledge, Oracle doesn't have a right() function, and the sql provided 
didn't work in Oracle.  Also, I could have extensions with any number of 
digits, so I'm not sure that will work anyway.

Still looking for a solid solution.

Thanks,

Dave


 Return-Path: <[EMAIL PROTECTED]> Sat Feb 10 14:30:57 2007
Received: from houseoffusion.com [64.118.74.249] by mail.bizbreeze.com with 
SMTP;
Sat, 10 Feb 2007 14:30:57 -0500
Received: from LOCALHOST by LOCALHOST
with ESMTP id 4A591C6C01270B47B496A2614E792BB1
Sat, 10 Feb 2007 14:34:57 -0500
Subject: Re: SOT: SQL Question
From: Jim Wright <[EMAIL PROTECTED]>
Content-Type: text/plain
Precedence: bulk
Reply-To: cf-talk@houseoffusion.com
To: CF-Talk 
Date: Sat, 10 Feb 2007 14:34:11 -0500
References: <[EMAIL PROTECTED]>
Message-ID: <[EMAIL PROTECTED]>
X-SmarterMail-Spam: SPF_None
X-Rcpt-To: <[EMAIL PROTECTED]> 

Kris Jones wrote:
> select case left(right(path,4),1) when '.' then right(path,3) else '' end
> 

This will work on SQL Server (assuming all of your extensions are three 
characters), and possibly on Oracle(I think it now supports CASE). In 
your message you wanted a T-SQL solution, but said your database was 
Oracle. Did you mean to say a PL/SQL or possible just SQL solution? 
T-SQL is the proprietary version of SQL specific to Sybase and SQL Server.



~|
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:269438
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: SOT: SQL Question

[James Holmes]

Actually you can write an anonymous PL/SQL block inside a cfquery tag.
There's a bug wherein line returns will cause an error in the JDBC
driver, so the block must be all on one line, but it will work.

Anyway, SUBSTR() will work like a right() function when -1 is used as
the starting position:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions119a.htm

And here's the CASE syntax for Oracle:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions5a.htm


On 2/11/07, Dave Phillips <[EMAIL PROTECTED]> wrote:
> Jim,
>
> Yeah, I typoed. Sorry, worked with SQL Server a lot. I did mean a PL/SQL 
> solution, however, I cannot write a PL/SQL function to utilize, so whatever 
> SQL I use, it has to work inside a 
>
> To my knowledge, Oracle doesn't have a right() function, and the sql provided 
> didn't work in Oracle. Also, I could have extensions with any number of 
> digits, so I'm not sure that will work anyway.
>
> Still looking for a solid solution.
>
> Kris Jones wrote:
> > select case left(right(path,4),1) when '.' then right(path,3) else '' end
> >
>
> This will work on SQL Server (assuming all of your extensions are three
> characters), and possibly on Oracle(I think it now supports CASE). In
> your message you wanted a T-SQL solution, but said your database was
> Oracle. Did you mean to say a PL/SQL or possible just SQL solution?
> T-SQL is the proprietary version of SQL specific to Sybase and SQL Server.

-- 
CFAJAX docs and other useful articles:
http://www.bifrost.com.au/blog/

~|
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:269440
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SOT sql question

[Will Swain]

IN works the other way round I think - say you have a list of values and you
want to pull all records with any of those values:

WHERE name IN (will,john,ray)


-Original Message-
From: Mark Fuqua [mailto:[EMAIL PROTECTED] 
Sent: 09 January 2008 16:57
To: CF-Talk
Subject: SOT sql question

I think I need to be using 'IN' within my WHERE clause but I can't seem to
get it to work.  I have a column with a comma delimited list.  This is the
latest attempt and it craps out too.

 

SELECT..

 

WHERE JobFileJob = #session.jobId# AND  '#session.UserRole#' IN
(JobFileAccessLevel)

 

JobFileAccessLevel is a field in the database table that contains a comma
delimited list of access levels.

 

Maybe I'm barking up the wrong tree entirely.  What I'm trying to do is.

 

...WHERE jobFileJob=#session.jobId# and jobFileAccessLevel CONTAINS
'#session.UserRole#'

 

But I don't think 'contains' exists.

 

Thanks,

 

Mark






~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296267
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SOT sql question

[Charlie Griefer]

On Jan 9, 2008 8:56 AM, Mark Fuqua <[EMAIL PROTECTED]> wrote:
> I think I need to be using 'IN' within my WHERE clause but I can't seem to
> get it to work.  I have a column with a comma delimited list.  This is the
> latest attempt and it craps out too.
>
> SELECT..
> ...WHERE JobFileJob = #session.jobId# AND  '#session.UserRole#' IN
> (JobFileAccessLevel)
>
> JobFileAccessLevel is a field in the database table that contains a comma
> delimited list of access levels.

you're doing it backwards :)

SELECT foo FROM bar WHERE foobar IN (a,b,c,d)

says "give me records where the value of foobar is either a OR b OR c
Or d.  "IN", is a shorthand way of writing out a number of OR
statements.

it'd be wrong of me to not state here that generally speaking, storing
comma delimited values in a relational database table is (again,
generally) not a good idea and defeats the purpose of a relational
database.  those values should (likely) be broken out into a new table
as individual rows (with an ID column having a FK relationship back to
the original table).


-- 
"Scientists tell us that the fastest animal on earth, with a top speed
of 120 feet per second, is a cow that has been dropped out of a
helicopter." - Dave Barry

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296268
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SOT sql question

[Marco Antonio C. Santos]

Mark,

I'll like to suggest you to use DataMgr. DataMgr is a great time saver tool
and could be the answer to your needs.

http://datamgr.riaforge.org/

Cheers
Marco Antonio C. Santos

On Jan 9, 2008 2:56 PM, Mark Fuqua <[EMAIL PROTECTED]> wrote:

> I think I need to be using 'IN' within my WHERE clause but I can't seem to
> get it to work.  I have a column with a comma delimited list.  This is the
> latest attempt and it craps out too.
>
>
>
> SELECT..
>
>
>
> ...WHERE JobFileJob = #session.jobId# AND  '#session.UserRole#' IN
> (JobFileAccessLevel)
>
>
>
> JobFileAccessLevel is a field in the database table that contains a comma
> delimited list of access levels.
>
>
>
> Maybe I'm barking up the wrong tree entirely.  What I'm trying to do is.
>
>
>
> ..WHERE jobFileJob=#session.jobId# and jobFileAccessLevel CONTAINS
> '#session.UserRole#'
>
>
>
> But I don't think 'contains' exists.
>
>
>
> Thanks,
>
>
>
> Mark
>
>
>
>
> 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296275
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SOT sql question

[Mark Fuqua]

My mechanic likes to tell me that I should inform him what my car is/is not
doing and let him do the diagnosing.  Maybe I should try that with this
problem also.

Using a form, 'JobFileUpload.cfm', files are uploaded to the server to a
folder based on JobId and a then a record of jobId, file name, file type and
access level/levels is inserted in the database table 'jobFiles'.  I know I
could do a another table with JobFileId's and Access levels, instead of a
list of access levels, but it seems cleaner this way.  

On JobFileList.cfm, I want to filter on jobId and the current users access
level which is stored in #session.PlumUserRoles#
 
Any idea how I might do that?



Thanks,

Mark

-Original Message-
From: Charlie Griefer [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 09, 2008 12:11 PM
To: CF-Talk
Subject: Re: SOT sql question

On Jan 9, 2008 8:56 AM, Mark Fuqua <[EMAIL PROTECTED]> wrote:
> I think I need to be using 'IN' within my WHERE clause but I can't seem to
> get it to work.  I have a column with a comma delimited list.  This is the
> latest attempt and it craps out too.
>
> SELECT..
> ...WHERE JobFileJob = #session.jobId# AND  '#session.UserRole#' IN
> (JobFileAccessLevel)
>
> JobFileAccessLevel is a field in the database table that contains a comma
> delimited list of access levels.

you're doing it backwards :)

SELECT foo FROM bar WHERE foobar IN (a,b,c,d)

says "give me records where the value of foobar is either a OR b OR c
Or d.  "IN", is a shorthand way of writing out a number of OR
statements.

it'd be wrong of me to not state here that generally speaking, storing
comma delimited values in a relational database table is (again,
generally) not a good idea and defeats the purpose of a relational
database.  those values should (likely) be broken out into a new table
as individual rows (with an ID column having a FK relationship back to
the original table).


-- 
"Scientists tell us that the fastest animal on earth, with a top speed
of 120 feet per second, is a cow that has been dropped out of a
helicopter." - Dave Barry



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296279
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SOT sql question

[Ian Skinner]

Mark Fuqua wrote:
> I know I could do a another table with JobFileId's and Access levels, instead 
> of a
> list of access levels, but it seems cleaner this way.  
>   
It is not.  You have denormalized your data in such away that doing the 
type of select you want to do is very difficult.  If you had a 
normalized database scheme with this data in a related table it would be 
a trivial join and where clause to get the exact records you want.
> On JobFileList.cfm, I want to filter on jobId and the current users access 
> level which is stored in #session.PlumUserRoles#
>  
> Any idea how I might do that?
If you insist on using this database design you are going to have to use 
the LIKE operator in the where clause with a bunch of possibilities.  
Lets say you want to look for an AccessLevel of '3'.  You are going to 
have to write a where clause something like.

WHERE accessLevel LIKE '%,3,$' OR accessLevel LIKE '%,3' OR accessLevel 
LIKE '3,%' ect.

And this does not even address the problems of making sure you do not 
match threes in the possible values of '13', '32', '33' ect.

Contrast this with this.

SELECT stuff
FROM aTable JOIN bTable ON aTable.key = bTable.fKey
WHERE bTable.JobFileAccessLevel = 3

Personally I find the latter much cleaner.






~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296284
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: SOT sql question

[Mark Fuqua]

Nuf said.  I'll do it the right way.

-Original Message-
From: Ian Skinner [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 09, 2008 1:12 PM
To: CF-Talk
Subject: Re: SOT sql question

Mark Fuqua wrote:
> I know I could do a another table with JobFileId's and Access levels,
instead of a
> list of access levels, but it seems cleaner this way.  
>   
It is not.  You have denormalized your data in such away that doing the 
type of select you want to do is very difficult.  If you had a 
normalized database scheme with this data in a related table it would be 
a trivial join and where clause to get the exact records you want.
> On JobFileList.cfm, I want to filter on jobId and the current users access
level which is stored in #session.PlumUserRoles#
>  
> Any idea how I might do that?
If you insist on using this database design you are going to have to use 
the LIKE operator in the where clause with a bunch of possibilities.  
Lets say you want to look for an AccessLevel of '3'.  You are going to 
have to write a where clause something like.

WHERE accessLevel LIKE '%,3,$' OR accessLevel LIKE '%,3' OR accessLevel 
LIKE '3,%' ect.

And this does not even address the problems of making sure you do not 
match threes in the possible values of '13', '32', '33' ect.

Contrast this with this.

SELECT stuff
FROM aTable JOIN bTable ON aTable.key = bTable.fKey
WHERE bTable.JobFileAccessLevel = 3

Personally I find the latter much cleaner.








~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296290
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: SOT: SQL question (Resolved)

[Cutter (CF-Talk)]

This is why I love this list! You guys (and the few gals) are the bomb!

Cutter

Mark A. Kruger - CFG wrote:
> Yes but Query of queries DOES support the union key word.
> 
> -Mark
> 
>   -Original Message-
>   From: Cutter (CF-Talk) [mailto:[EMAIL PROTECTED]
>   Sent: Monday, February 23, 2004 2:01 PM
>   To: CF-Talk
>   Subject: Re: SOT: SQL question
> 
>   As listed in the original post, my ISP is using MySQL 3.23 (which has no
>   support for union)...
> 
>   Cutter
> 
>   Ubqtous wrote:
> 
>   > Cutter,
>   >
>   > On 2/23/2004 at 14:50, you wrote:
>   >
>   > CCT> **qEventList QofQ**
>   > CCT> select  *
>   > CCT> fromqHoliday,
>   > CCT> qHoliday2
>   > CCT> order bySDate
>   >
>   > maybe try this in your QoQ:
>   >
>   > select * from qHoliday
>   > union
>   > select * from qHoliday2
>   > order by SDate
>   >
>   > ~ Ubqtous ~
>   >
>
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

SOT: SQL Question - Could CF make this easier?

[Jillian Carroll]

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

Re: SOT: SQL Question - Could CF make this easier?

[[EMAIL PROTECTED]]

You can do it easier with SQL by using NOT IN


... WHERE Description NOT IN ('ANISE', 'APPLES',.)

HTH

Dick


On Wednesday, June 11, 2003, at 11:37 AM, Jillian Carroll wrote:

> 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

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4