RE: Query a Query or SQL Syntax (Not IN)
no problem, man. WHERE head IN (ass)? :) christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -Original Message- From: Paul Sizemore [mailto:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 10:14 AM To: CF-Talk Subject: RE: Query a Query or SQL Syntax (Not IN) I was out of the office on Friday, but this worked like a charm. Thanks for all your help. On the name thing, I'm just following SOP here. If you can make someone else have a potty mouth, do it (it's great fun in meetings). At least I don't need a header table or variable (maybe I can work that in later). -Original Message- From: Christopher Olive [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 11:20 AM To: CF-Talk Subject: RE: Query a Query or SQL Syntax (Not IN) should you have a ValueList(asstaken.assname) in your last recordset? as it is, you're trying to exclude a *query variable#, not a list of names. IE: Select empl from AssList,asstaken Where empl NOT IN (#ValueList(AssTaken.assname)#) as a side note, very...interesting variable naming. christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -Original Message- From: Paul Sizemore [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 11:01 AM To: CF-Talk Subject: Query a Query or SQL Syntax (Not IN) I've got two queries from different data sources, and I want to find records in the First, that are not in the second (a non-compliance report) Shouldn't I be able to do a NOT IN in a Query of Query? My code is as follows (Ass is short for Associate): Select AssName from Intranet.PBS.App_StraightTalk Select empl,locnum,first_name,last_name,last_hire From associate Where last_hire > '12/24/2001' Select empl from AssList,asstaken Where empl NOT IN (AssTaken) What am I doing wrong, or how should I do this? __ 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: Query a Query or SQL Syntax (Not IN)
I was out of the office on Friday, but this worked like a charm. Thanks for all your help. On the name thing, I'm just following SOP here. If you can make someone else have a potty mouth, do it (it's great fun in meetings). At least I don't need a header table or variable (maybe I can work that in later). -Original Message- From: Christopher Olive [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 11:20 AM To: CF-Talk Subject: RE: Query a Query or SQL Syntax (Not IN) should you have a ValueList(asstaken.assname) in your last recordset? as it is, you're trying to exclude a *query variable#, not a list of names. IE: Select empl from AssList,asstaken Where empl NOT IN (#ValueList(AssTaken.assname)#) as a side note, very...interesting variable naming. christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -Original Message- From: Paul Sizemore [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 11:01 AM To: CF-Talk Subject: Query a Query or SQL Syntax (Not IN) I've got two queries from different data sources, and I want to find records in the First, that are not in the second (a non-compliance report) Shouldn't I be able to do a NOT IN in a Query of Query? My code is as follows (Ass is short for Associate): Select AssName from Intranet.PBS.App_StraightTalk Select empl,locnum,first_name,last_name,last_hire From associate Where last_hire > '12/24/2001' Select empl from AssList,asstaken Where empl NOT IN (AssTaken) What am I doing wrong, or how should I do this? __ 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: Query a Query or SQL Syntax (Not IN)
I love your field names "AssName"! Brian Yager President - North AL Cold Fusion Users Group Sr. Systems Analyst NCCIM/CIC [EMAIL PROTECTED] (256) 842-8342 -Original Message- From: Paul Sizemore [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 10:01 AM To: CF-Talk Subject: Query a Query or SQL Syntax (Not IN) I've got two queries from different data sources, and I want to find records in the First, that are not in the second (a non-compliance report) Shouldn't I be able to do a NOT IN in a Query of Query? My code is as follows (Ass is short for Associate): Select AssName from Intranet.PBS.App_StraightTalk Select empl,locnum,first_name,last_name,last_hire From associate Where last_hire > '12/24/2001' Select empl from AssList,asstaken Where empl NOT IN (AssTaken) What am I doing wrong, or how should I do this? __ 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: Query a Query or SQL Syntax (Not IN)
Gotta love your query names! -Original Message- From: Paul Sizemore [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 10:01 AM To: CF-Talk Subject: Query a Query or SQL Syntax (Not IN) I've got two queries from different data sources, and I want to find records in the First, that are not in the second (a non-compliance report) Shouldn't I be able to do a NOT IN in a Query of Query? My code is as follows (Ass is short for Associate): Select AssName from Intranet.PBS.App_StraightTalk Select empl,locnum,first_name,last_name,last_hire From associate Where last_hire > '12/24/2001' Select empl from AssList,asstaken Where empl NOT IN (AssTaken) What am I doing wrong, or how should I do this? __ 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: Query a Query or SQL Syntax (Not IN)
should you have a ValueList(asstaken.assname) in your last recordset? as it is, you're trying to exclude a *query variable#, not a list of names. IE: Select empl from AssList,asstaken Where empl NOT IN (#ValueList(AssTaken.assname)#) as a side note, very...interesting variable naming. christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -Original Message- From: Paul Sizemore [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 11:01 AM To: CF-Talk Subject: Query a Query or SQL Syntax (Not IN) I've got two queries from different data sources, and I want to find records in the First, that are not in the second (a non-compliance report) Shouldn't I be able to do a NOT IN in a Query of Query? My code is as follows (Ass is short for Associate): Select AssName from Intranet.PBS.App_StraightTalk Select empl,locnum,first_name,last_name,last_hire From associate Where last_hire > '12/24/2001' Select empl from AssList,asstaken Where empl NOT IN (AssTaken) What am I doing wrong, or how should I do this? __ 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