RE: Not in?
If you're using Oracle you can do query1 MINUS query2 Alex -Original Message- From: Kris Pilles [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 9:46 AM To: CF-Talk Subject: Not in? I have 2 queries one that contains the enitre recordset of the table and 1 that contains my flagged results. What I need to do is find a way to display all of the records that aren't part of query #2's recordset. Please help KP Kris Pilles Website Manager Western Suffolk BOCES 507 Deer Park Rd., Building C Phone: 631-549-4900 x 267 E-mail: [EMAIL PROTECTED] __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Not in?
Ummm...mabye I'm mising something here, but couldn't you run a query that pulled all records that aren't flagged? That would seem to me to give you all records from query 1 that aren't found in query 2. HTH Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com - Original Message - From: "Kris Pilles" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, September 18, 2002 9:46 AM Subject: Not in? > I have 2 queries one that contains the enitre recordset of the table and > 1 that contains my flagged results. > > What I need to do is find a way to display all of the records that > aren't part of query #2's recordset. > > Please help > > KP > > Kris Pilles > Website Manager > Western Suffolk BOCES > 507 Deer Park Rd., Building C > Phone: 631-549-4900 x 267 > E-mail: [EMAIL PROTECTED] > > > __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Not in?
Where Field NOT IN #ValueList(query1.field)# -Original Message- From: Kris Pilles [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 12:46 PM To: CF-Talk Subject: Not in? I have 2 queries one that contains the enitre recordset of the table and 1 that contains my flagged results. What I need to do is find a way to display all of the records that aren't part of query #2's recordset. Please help KP Kris Pilles Website Manager Western Suffolk BOCES 507 Deer Park Rd., Building C Phone: 631-549-4900 x 267 E-mail: [EMAIL PROTECTED] __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Not in?
Your topic makes me think you already know...Use NOT IN in your SQL statement. SELECT * FROM Table WHERE ID NOT IN (#quotedValueList(flaggedRecordSet.id)#) -- jon mailto:[EMAIL PROTECTED] Wednesday, September 18, 2002, 12:46:21 PM, you wrote: KP> I have 2 queries one that contains the enitre recordset of the table and KP> 1 that contains my flagged results. KP> What I need to do is find a way to display all of the records that KP> aren't part of query #2's recordset. KP> Please help KP> KP KP> Kris Pilles KP> Website Manager KP> Western Suffolk BOCES KP> 507 Deer Park Rd., Building C KP> Phone: 631-549-4900 x 267 KP> E-mail: [EMAIL PROTECTED] KP> __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Not in?
A simple way of doing this would be: select firstname from tableA where recordid not in (select recordid from TableB) You may want look into a join depending on the use. Mark W. Breneman -Macromedia Certified ColdFusion Developer -Network / Web Server Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -Original Message- From: Kris Pilles [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 11:46 AM To: CF-Talk Subject: Not in? I have 2 queries one that contains the enitre recordset of the table and 1 that contains my flagged results. What I need to do is find a way to display all of the records that aren't part of query #2's recordset. Please help KP Kris Pilles Website Manager Western Suffolk BOCES 507 Deer Park Rd., Building C Phone: 631-549-4900 x 267 E-mail: [EMAIL PROTECTED] __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Not in?
Thanks! I'm having a rough day... I thought it was not in but I figured I would ask KP -Original Message- From: Mark W. Breneman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 1:20 PM To: CF-Talk Subject: RE: Not in? A simple way of doing this would be: select firstname from tableA where recordid not in (select recordid from TableB) You may want look into a join depending on the use. Mark W. Breneman -Macromedia Certified ColdFusion Developer -Network / Web Server Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -Original Message- From: Kris Pilles [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 11:46 AM To: CF-Talk Subject: Not in? I have 2 queries one that contains the enitre recordset of the table and 1 that contains my flagged results. What I need to do is find a way to display all of the records that aren't part of query #2's recordset. Please help KP Kris Pilles Website Manager Western Suffolk BOCES 507 Deer Park Rd., Building C Phone: 631-549-4900 x 267 E-mail: [EMAIL PROTECTED] __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Not in?
I think there is better performance, and only one db call, by doing a left outer join on the table you want all records from and adding "where table2column is not null". That is, of course, unless they are the same table, but you could self-join the table with the same technique... Why two recordsets instead of two tables? >>-Original Message- >>From: Mark W. Breneman [mailto:[EMAIL PROTECTED]] >>Sent: Wednesday, September 18, 2002 12:20 PM >>To: CF-Talk >>Subject: RE: Not in? >> >>A simple way of doing this would be: >> >>select firstname >>from tableA >>where recordid not in (select recordid from TableB) >> >>You may want look into a join depending on the use. >> >>Mark W. Breneman >>-Macromedia Certified ColdFusion Developer >>-Network / Web Server Administrator >> Vivid Media >> [EMAIL PROTECTED] >> www.vividmedia.com >> 608.270.9770 >> >>-Original Message- >>From: Kris Pilles [mailto:[EMAIL PROTECTED]] >>Sent: Wednesday, September 18, 2002 11:46 AM >>To: CF-Talk >>Subject: Not in? >> >> >>I have 2 queries one that contains the enitre recordset of the table and >>1 that contains my flagged results. >> >>What I need to do is find a way to display all of the records that >>aren't part of query #2's recordset. >> >>Please help >> >>KP >> >>Kris Pilles >>Website Manager >>Western Suffolk BOCES >>507 Deer Park Rd., Building C >>Phone: 631-549-4900 x 267 >>E-mail: [EMAIL PROTECTED] >> >> >> >> __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Not in?
> I have 2 queries one that contains the enitre recordset of the table and > 1 that contains my flagged results. > What I need to do is find a way to display all of the records that > aren't part of query #2's recordset. > Please help SELECT * FROM mytable WHERE myprimarykey NOT IN (#valuelist(myquery.mycolumn)#) assuming your pimary key is a number ... if not, use #quotedvaluelist(myquery.mycolumn)# hth S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Not in?
Exactly Mark. I was just going to suggest that rather than making CF work on it. -Original Message- From: Mark W. Breneman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 1:20 PM To: CF-Talk Subject: RE: Not in? A simple way of doing this would be: select firstname from tableA where recordid not in (select recordid from TableB) You may want look into a join depending on the use. Mark W. Breneman -Macromedia Certified ColdFusion Developer -Network / Web Server Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -Original Message- From: Kris Pilles [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 11:46 AM To: CF-Talk Subject: Not in? I have 2 queries one that contains the enitre recordset of the table and 1 that contains my flagged results. What I need to do is find a way to display all of the records that aren't part of query #2's recordset. Please help KP Kris Pilles Website Manager Western Suffolk BOCES 507 Deer Park Rd., Building C Phone: 631-549-4900 x 267 E-mail: [EMAIL PROTECTED] __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: `NOT in` CFQuery problem
The problem with your code is that at the last list append, there would be a
comma at the end. :P Finally, the quotedvaluelist function sets your values
inside singlequotes.
do this
SELECT semail FROM survey
SELECT * FROM members
WHERE subscribe_date = #CreateODBCDate(DateAdd("d", -30, today))#
AND (email NOT in '#(QuotedValueList(surveyemail))#')
>
>
>
>
>SELECT semail FROM survey
>
>
>
>
>
>
>
>
>
>SELECT * FROM members
>WHERE subscribe_date = #CreateODBCDate(DateAdd("d", -30, today))#
>AND (email NOT in '#surveyemail#')
>
>
>
>Will
>
~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: `NOT in` CFQuery problem
Thanks for the quick response. Unfortunately it didn't work though :(
I changed the 2nd query from what you gave me, because one of your brackets
generated an error
SELECT * FROM members
WHERE subscribe_date = #CreateODBCDate(DateAdd("d", -30, today))#
AND (email NOT in '#QuotedValueList(surveyemail)#')
...but afterwards got an error with the QuoteValueList function:
"Parameter 1 of function QuotedValueList which is now "surveyemail" must be
a fully qualified column name of the form Query.ColumnName"
Any ideas?
Cheers
Will
- Original Message -
From: "Koo Pai Lao" <[EMAIL PROTECTED]>
Newsgroups: cf-talk
Sent: Monday, September 24, 2001 11:02 PM
Subject: Re: `NOT in` CFQuery problem
> The problem with your code is that at the last list append, there would be
a
> comma at the end. :P Finally, the quotedvaluelist function sets your
values
> inside singlequotes.
>
> do this
>
>
>
>
> SELECT semail FROM survey
>
>
>
>
>
>
>
>
>
> SELECT * FROM members
> WHERE subscribe_date = #CreateODBCDate(DateAdd("d", -30, today))#
> AND (email NOT in '#(QuotedValueList(surveyemail))#')
>
>
>
>
>
> >
> >
> >
> >
> >SELECT semail FROM survey
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >SELECT * FROM members
> >WHERE subscribe_date = #CreateODBCDate(DateAdd("d", -30, today))#
> >AND (email NOT in '#surveyemail#')
> >
> >
> >
> >Will
> >
>
~~
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
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: `NOT in` CFQuery problem
You just have to do what the error message is asking you to do - output the
query.columnname, like so: (I can't remember what you named your first
query, so you'll have to change the query name.)
SELECT * FROM members
WHERE subscribe_date = #CreateODBCDate(DateAdd("d", -30, today))#
AND (email NOT in '#QuotedValueList(firstquery.semail)#')
Deanna Schneider
Interactive Media Developer
[EMAIL PROTECTED]
~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: `NOT in` CFQuery problem
I did, but it didn't work. Then I realised that I had quote the function
('#QuotedValueList(firstquery.semail)#') which was causing the error (the
first and last emails in the list came out as ''[EMAIL PROTECTED]
Thanks
Will
- Original Message -
From: "Deanna Schneider" <[EMAIL PROTECTED]>
Newsgroups: cf-talk
Sent: Tuesday, September 25, 2001 2:08 PM
Subject: Re: `NOT in` CFQuery problem
> You just have to do what the error message is asking you to do - output
the
> query.columnname, like so: (I can't remember what you named your first
> query, so you'll have to change the query name.)
>
>
> SELECT * FROM members
> WHERE subscribe_date = #CreateODBCDate(DateAdd("d", -30, today))#
> AND (email NOT in '#QuotedValueList(firstquery.semail)#')
>
>
>
>
> Deanna Schneider
> Interactive Media Developer
> [EMAIL PROTECTED]
>
>
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
