RE: Subquery from the same table: Is it possible??
Is it possible to have a subquery from the same table to output data based on a certain condition? Yes. It's called a correlated subquery. It's described very well in the SQL Server Books Online: Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book. USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_id) Here is the result set: au_lname au_fname WhiteJohnson GreenMarjorie Carson Cheryl Straight Dean Locksley Charlene Blotchet-Halls Reginald del Castillo Innes Panteley Sylvia Ringer Albert (9 row(s) affected) Unlike most of the subqueries shown earlier, the subquery in this statement cannot be resolved independently of the main query. It needs a value for authors.au_id, but this value is a variable. It changes as Microsoft® SQL Server(tm) examines different rows of the authors table. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ 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: Subquery from the same table: Is it possible??
Yes, but you may need to alias the table name on the external query to avoid conflicts. SELECT field1 FROM table AS exttable WHERE id IN (SELECT id FROM table WHERE field2 = exttable.field1) I know the example could be written without the subquery, but is just an example of the aliasing needed in this type of setup. -Justin Scott, Lead Developer Sceiron Internet Services, Inc. http://www.sceiron.com - Original Message - From: James Taavon [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 10:10 AM Subject: Subquery from the same table: Is it possible?? Is it possible to have a subquery from the same table to output data based on a certain condition? __ 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: Subquery from the same table: Is it possible??
ok. Interesting. Here is my situation. I will try to explain it has best I can. I have a response form that inserts the user into one field via session variable. The other field needs another name based on the role of the first. ROLE is a field in my database. Here is an example: USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller If Tom Wendel opens a response form, his name is inserted into a field called 'sender'. I want the 'receiver' to be Denise Johnson. This is but one possible scenario as I have one key user for a manager and several users that fall under the same manager. Total of about 6 managers each with one key user to manage the users. Is this possible to use a subquery based on the way the data is structued now or do I need to make a change? I tried something like this, but got an error which did not surprise me. cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whereIN (select fullname from tbl_users where username = '#session.username#') AND role LIKE 'key_user%' /cfquery -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:18 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Is it possible to have a subquery from the same table to output data based on a certain condition? Yes. It's called a correlated subquery. It's described very well in the SQL Server Books Online: Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book. USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_id) Here is the result set: au_lname au_fname WhiteJohnson GreenMarjorie Carson Cheryl Straight Dean Locksley Charlene Blotchet-Halls Reginald del Castillo Innes Panteley Sylvia Ringer Albert (9 row(s) affected) Unlike most of the subqueries shown earlier, the subquery in this statement cannot be resolved independently of the main query. It needs a value for authors.au_id, but this value is a variable. It changes as Microsoft® SQL Server(tm) examines different rows of the authors table. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ 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: Subquery from the same table: Is it possible??
James, You wrote ** whereIN (select fullname from tbl_users where username = '#session.username#') AND role LIKE 'key_user%' *** Where WHAT ?you have to specify a column with the same data type for your sub select to use as IN: WHERE fullname IN (Select fullname) -mk -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 9:47 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? ok. Interesting. Here is my situation. I will try to explain it has best I can. I have a response form that inserts the user into one field via session variable. The other field needs another name based on the role of the first. ROLE is a field in my database. Here is an example: USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller If Tom Wendel opens a response form, his name is inserted into a field called 'sender'. I want the 'receiver' to be Denise Johnson. This is but one possible scenario as I have one key user for a manager and several users that fall under the same manager. Total of about 6 managers each with one key user to manage the users. Is this possible to use a subquery based on the way the data is structued now or do I need to make a change? I tried something like this, but got an error which did not surprise me. cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whereIN (select fullname from tbl_users where username = '#session.username#') AND role LIKE 'key_user%' /cfquery -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:18 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Is it possible to have a subquery from the same table to output data based on a certain condition? Yes. It's called a correlated subquery. It's described very well in the SQL Server Books Online: Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book. USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_id) Here is the result set: au_lname au_fname WhiteJohnson GreenMarjorie Carson Cheryl Straight Dean Locksley Charlene Blotchet-Halls Reginald del Castillo Innes Panteley Sylvia Ringer Albert (9 row(s) affected) Unlike most of the subqueries shown earlier, the subquery in this statement cannot be resolved independently of the main query. It needs a value for authors.au_id, but this value is a variable. It changes as Microsoft® SQL Server(tm) examines different rows of the authors table. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ 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: Subquery from the same table: Is it possible??
cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whererole = ( select 'key_' role as keyrole from tbl_users where username = '#session.username#') /cfquery See if that works for you. It's an educated guess. - Matt Small -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:47 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? ok. Interesting. Here is my situation. I will try to explain it has best I can. I have a response form that inserts the user into one field via session variable. The other field needs another name based on the role of the first. ROLE is a field in my database. Here is an example: USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller If Tom Wendel opens a response form, his name is inserted into a field called 'sender'. I want the 'receiver' to be Denise Johnson. This is but one possible scenario as I have one key user for a manager and several users that fall under the same manager. Total of about 6 managers each with one key user to manage the users. Is this possible to use a subquery based on the way the data is structued now or do I need to make a change? I tried something like this, but got an error which did not surprise me. cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whereIN (select fullname from tbl_users where username = '#session.username#') AND role LIKE 'key_user%' /cfquery -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:18 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Is it possible to have a subquery from the same table to output data based on a certain condition? Yes. It's called a correlated subquery. It's described very well in the SQL Server Books Online: Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book. USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_id) Here is the result set: au_lname au_fname WhiteJohnson GreenMarjorie Carson Cheryl Straight Dean Locksley Charlene Blotchet-Halls Reginald del Castillo Innes Panteley Sylvia Ringer Albert (9 row(s) affected) Unlike most of the subqueries shown earlier, the subquery in this statement cannot be resolved independently of the main query. It needs a value for authors.au_id, but this value is a variable. It changes as MicrosoftR SQL Server(tm) examines different rows of the authors table. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ 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: Subquery from the same table: Is it possible??
almost like the same output I got with this, but it produces the same name in both fields. My code prodcues two instances of USER and your code produces two instances of the KEY USER. Getting closer though... cfquery name=GetKeyUser datasource=lsar_beta select fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:06 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whererole = ( select 'key_' role as keyrole from tbl_users where username = '#session.username#') /cfquery See if that works for you. It's an educated guess. - Matt Small -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:47 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? ok. Interesting. Here is my situation. I will try to explain it has best I can. I have a response form that inserts the user into one field via session variable. The other field needs another name based on the role of the first. ROLE is a field in my database. Here is an example: USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller If Tom Wendel opens a response form, his name is inserted into a field called 'sender'. I want the 'receiver' to be Denise Johnson. This is but one possible scenario as I have one key user for a manager and several users that fall under the same manager. Total of about 6 managers each with one key user to manage the users. Is this possible to use a subquery based on the way the data is structued now or do I need to make a change? I tried something like this, but got an error which did not surprise me. cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whereIN (select fullname from tbl_users where username = '#session.username#') AND role LIKE 'key_user%' /cfquery -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:18 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Is it possible to have a subquery from the same table to output data based on a certain condition? Yes. It's called a correlated subquery. It's described very well in the SQL Server Books Online: Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book. USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_id) Here is the result set: au_lname au_fname WhiteJohnson GreenMarjorie Carson Cheryl Straight Dean Locksley Charlene Blotchet-Halls Reginald del Castillo Innes Panteley Sylvia Ringer Albert (9 row(s) affected) Unlike most of the subqueries shown earlier, the subquery in this statement cannot be resolved independently of the main query. It needs a value for authors.au_id, but this value is a variable. It changes as MicrosoftR SQL Server(tm) examines different rows of the authors table. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ 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: Subquery from the same table: Is it possible??
You have to join the two tables even if it is the same one. However, this particular query makes no sense. This type of technique is best with the key manager where they have a connected key so you do something like this: Select * from tbl_users where role = (select role from tbl_users a,tbl_users b where a.role_owner=b.role and a.username = '#session.username#') this would be a case where role_owner is the manager and the role_owner key is matched to the role key for the subordinates. Margaret cfquery name=GetKeyUser datasource=lsar_beta select fullname, role fromtbl_users a where fullname IN (select fullname from tbl_users a,tbl_users b where a.username=b.username and username = '#session.username#') /cfquery -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 8:07 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? almost like the same output I got with this, but it produces the same name in both fields. My code prodcues two instances of USER and your code produces two instances of the KEY USER. Getting closer though... cfquery name=GetKeyUser datasource=lsar_beta select fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:06 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whererole = ( select 'key_' role as keyrole from tbl_users where username = '#session.username#') /cfquery See if that works for you. It's an educated guess. - Matt Small -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:47 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? ok. Interesting. Here is my situation. I will try to explain it has best I can. I have a response form that inserts the user into one field via session variable. The other field needs another name based on the role of the first. ROLE is a field in my database. Here is an example: USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller If Tom Wendel opens a response form, his name is inserted into a field called 'sender'. I want the 'receiver' to be Denise Johnson. This is but one possible scenario as I have one key user for a manager and several users that fall under the same manager. Total of about 6 managers each with one key user to manage the users. Is this possible to use a subquery based on the way the data is structued now or do I need to make a change? I tried something like this, but got an error which did not surprise me. cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whereIN (select fullname from tbl_users where username = '#session.username#') AND role LIKE 'key_user%' /cfquery -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:18 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Is it possible to have a subquery from the same table to output data based on a certain condition? Yes. It's called a correlated subquery. It's described very well in the SQL Server Books Online: Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book. USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_id) Here is the result set: au_lname au_fname WhiteJohnson GreenMarjorie Carson Cheryl Straight Dean Locksley Charlene Blotchet-Halls Reginald del Castillo
RE: Subquery from the same table: Is it possible??
Ummm.. I don't quite understand how that code is returning two instances of the key_role unless you have it in the database twice. Am I missing something here? If it does produce two instances, are they both the same? If they are, use the distinct keyword: cfquery name=GetKeyUser datasource=lsar_beta select distinct fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:07 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? almost like the same output I got with this, but it produces the same name in both fields. My code prodcues two instances of USER and your code produces two instances of the KEY USER. Getting closer though... cfquery name=GetKeyUser datasource=lsar_beta select fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:06 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whererole = ( select 'key_' role as keyrole from tbl_users where username = '#session.username#') /cfquery See if that works for you. It's an educated guess. - Matt Small -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:47 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? ok. Interesting. Here is my situation. I will try to explain it has best I can. I have a response form that inserts the user into one field via session variable. The other field needs another name based on the role of the first. ROLE is a field in my database. Here is an example: USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller If Tom Wendel opens a response form, his name is inserted into a field called 'sender'. I want the 'receiver' to be Denise Johnson. This is but one possible scenario as I have one key user for a manager and several users that fall under the same manager. Total of about 6 managers each with one key user to manage the users. Is this possible to use a subquery based on the way the data is structued now or do I need to make a change? I tried something like this, but got an error which did not surprise me. cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whereIN (select fullname from tbl_users where username = '#session.username#') AND role LIKE 'key_user%' /cfquery -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:18 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Is it possible to have a subquery from the same table to output data based on a certain condition? Yes. It's called a correlated subquery. It's described very well in the SQL Server Books Online: Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book. USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_id) Here is the result set: au_lname au_fname WhiteJohnson GreenMarjorie Carson Cheryl Straight Dean Locksley Charlene Blotchet-Halls Reginald del Castillo Innes Panteley Sylvia Ringer Albert (9 row(s) affected) Unlike most of the subqueries shown earlier, the subquery in this statement cannot be resolved independently of the main query. It needs a value for authors.au_id
RE: Subquery from the same table: Is it possible??
nope, only one person is designated as key role, but there are about six different ones, one for each manager. -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:22 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Ummm.. I don't quite understand how that code is returning two instances of the key_role unless you have it in the database twice. Am I missing something here? If it does produce two instances, are they both the same? If they are, use the distinct keyword: cfquery name=GetKeyUser datasource=lsar_beta select distinct fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:07 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? almost like the same output I got with this, but it produces the same name in both fields. My code prodcues two instances of USER and your code produces two instances of the KEY USER. Getting closer though... cfquery name=GetKeyUser datasource=lsar_beta select fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:06 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whererole = ( select 'key_' role as keyrole from tbl_users where username = '#session.username#') /cfquery See if that works for you. It's an educated guess. - Matt Small -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:47 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? ok. Interesting. Here is my situation. I will try to explain it has best I can. I have a response form that inserts the user into one field via session variable. The other field needs another name based on the role of the first. ROLE is a field in my database. Here is an example: USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller If Tom Wendel opens a response form, his name is inserted into a field called 'sender'. I want the 'receiver' to be Denise Johnson. This is but one possible scenario as I have one key user for a manager and several users that fall under the same manager. Total of about 6 managers each with one key user to manage the users. Is this possible to use a subquery based on the way the data is structued now or do I need to make a change? I tried something like this, but got an error which did not surprise me. cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whereIN (select fullname from tbl_users where username = '#session.username#') AND role LIKE 'key_user%' /cfquery -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:18 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Is it possible to have a subquery from the same table to output data based on a certain condition? Yes. It's called a correlated subquery. It's described very well in the SQL Server Books Online: Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book. USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_ID = authors.au_id) Here is the result set: au_lname au_fname WhiteJohnson GreenMarjorie Carson Cheryl Straight Dean Locksley Charlene Blotchet-Halls Reginald del Castillo
RE: Subquery from the same table: Is it possible??
Mark, Perhaps I am confusing you. On my output i am using the same query at least that is what i was planning to do. There is also a query just for the role. I am not sure if I can delete it and use the other instead. cfquery name=GetRole datasource=lsar_beta select role fromtbl_users where username = '#session.username#' /cfquery -- OUTPUT FOR KEY USER cfif getrole.role IS user_self Sandy Crisafulli cfelseif getrole.role CONTAINS trimble cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role IS user_miller cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS reich_godd cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS cooper cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS feinroth cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS edmounds cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelse /cfif -- OUTPUT FOR USER cfoutputinput type=Text name=sender value=#getkeyuser.fullname# size=20 readonly/cfoutput -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:21 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? nope, only one person is designated as key role, but there are about six different ones, one for each manager. -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:22 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Ummm.. I don't quite understand how that code is returning two instances of the key_role unless you have it in the database twice. Am I missing something here? If it does produce two instances, are they both the same? If they are, use the distinct keyword: cfquery name=GetKeyUser datasource=lsar_beta select distinct fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:07 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? almost like the same output I got with this, but it produces the same name in both fields. My code prodcues two instances of USER and your code produces two instances of the KEY USER. Getting closer though... cfquery name=GetKeyUser datasource=lsar_beta select fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:06 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whererole = ( select 'key_' role as keyrole from tbl_users where username = '#session.username#') /cfquery See if that works for you. It's an educated guess. - Matt Small -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 10:47 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? ok. Interesting. Here is my situation. I will try to explain it has best I can. I have a response form that inserts the user into one field via session variable. The other field needs another name based on the role of the first. ROLE is a field in my database. Here is an example: USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller If Tom Wendel opens a response form, his name is inserted into a field called 'sender'. I want the 'receiver' to be Denise Johnson. This is but one possible scenario as I have one key user for a manager and several users that fall under the same manager. Total of about 6 managers each with one key user to manage the users. Is this possible to use a subquery based on the way the data is structued now or do I need to make a change? I tried something like this, but got an error which did not surprise me. cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users
RE: Subquery from the same table: Is it possible??
I am very confused now as to what you are trying to accomplish. Maybe you could show a little more of your database. As I understand it, you have about six managers each with a key user. You have a number of employees whom each fall under a manager. When an employee responds to a form, the employee's fullname goes into sender and the manager's name goes into the receiver. Table USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller msmall Matt Small user_miller jtaavon James Taavonuser_miller jsmith Joe Smith key_user_jones wwilliams Wendy Williams user_jones tharms Todd Harms user_jones So when James Taavon responds, he becomes the sender and Denise Johnson is the receiver. When Todd Harms responds, he becomes the sender and Joe Smith is the receiver. Is this correct? I'm trying to get an understanding of how your db structure works so I can assist. - Matt Small -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:35 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Mark, Perhaps I am confusing you. On my output i am using the same query at least that is what i was planning to do. There is also a query just for the role. I am not sure if I can delete it and use the other instead. cfquery name=GetRole datasource=lsar_beta select role fromtbl_users where username = '#session.username#' /cfquery -- OUTPUT FOR KEY USER cfif getrole.role IS user_self Sandy Crisafulli cfelseif getrole.role CONTAINS trimble cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role IS user_miller cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS reich_godd cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS cooper cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS feinroth cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS edmounds cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelse /cfif -- OUTPUT FOR USER cfoutputinput type=Text name=sender value=#getkeyuser.fullname# size=20 readonly/cfoutput -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:21 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? nope, only one person is designated as key role, but there are about six different ones, one for each manager. -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:22 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Ummm.. I don't quite understand how that code is returning two instances of the key_role unless you have it in the database twice. Am I missing something here? If it does produce two instances, are they both the same? If they are, use the distinct keyword: cfquery name=GetKeyUser datasource=lsar_beta select distinct fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:07 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? almost like the same output I got with this, but it produces the same name in both fields. My code prodcues two instances of USER and your code produces two instances of the KEY USER. Getting closer though... cfquery name=GetKeyUser datasource=lsar_beta select fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:06 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whererole = ( select 'key_' role as keyrole from tbl_users where username = '#session.username#') /cfquery See if that works for you. It's an educated guess. - Matt Small
RE: Subquery from the same table: Is it possible??
CORRECT!! -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 2:24 PM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? I am very confused now as to what you are trying to accomplish. Maybe you could show a little more of your database. As I understand it, you have about six managers each with a key user. You have a number of employees whom each fall under a manager. When an employee responds to a form, the employee's fullname goes into sender and the manager's name goes into the receiver. Table USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller msmall Matt Small user_miller jtaavon James Taavonuser_miller jsmith Joe Smith key_user_jones wwilliams Wendy Williams user_jones tharms Todd Harms user_jones So when James Taavon responds, he becomes the sender and Denise Johnson is the receiver. When Todd Harms responds, he becomes the sender and Joe Smith is the receiver. Is this correct? I'm trying to get an understanding of how your db structure works so I can assist. - Matt Small -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:35 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Mark, Perhaps I am confusing you. On my output i am using the same query at least that is what i was planning to do. There is also a query just for the role. I am not sure if I can delete it and use the other instead. cfquery name=GetRole datasource=lsar_beta select role fromtbl_users where username = '#session.username#' /cfquery -- OUTPUT FOR KEY USER cfif getrole.role IS user_self Sandy Crisafulli cfelseif getrole.role CONTAINS trimble cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role IS user_miller cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS reich_godd cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS cooper cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS feinroth cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS edmounds cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelse /cfif -- OUTPUT FOR USER cfoutputinput type=Text name=sender value=#getkeyuser.fullname# size=20 readonly/cfoutput -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:21 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? nope, only one person is designated as key role, but there are about six different ones, one for each manager. -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:22 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Ummm.. I don't quite understand how that code is returning two instances of the key_role unless you have it in the database twice. Am I missing something here? If it does produce two instances, are they both the same? If they are, use the distinct keyword: cfquery name=GetKeyUser datasource=lsar_beta select distinct fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:07 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? almost like the same output I got with this, but it produces the same name in both fields. My code prodcues two instances of USER and your code produces two instances of the KEY USER. Getting closer though... cfquery name=GetKeyUser datasource=lsar_beta select fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:06 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whererole = ( select 'key_' role as keyrole
RE: Subquery from the same table: Is it possible??
If I have this correct, then the below query should work. I inserted the data that I made up into a table and I executed the following query on it - no problems. You may need to inspect your table for duplicates if you are getting more than one return. Or, it is possible for more than one instance of key_user_miller in the role field of your table? - Matt Small cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whererole = ( select 'key_' role as keyrole from tbl_users where username = '#session.username#') /cfquery -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 2:59 PM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? CORRECT!! -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 2:24 PM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? I am very confused now as to what you are trying to accomplish. Maybe you could show a little more of your database. As I understand it, you have about six managers each with a key user. You have a number of employees whom each fall under a manager. When an employee responds to a form, the employee's fullname goes into sender and the manager's name goes into the receiver. Table USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller msmall Matt Small user_miller jtaavon James Taavonuser_miller jsmith Joe Smith key_user_jones wwilliams Wendy Williams user_jones tharms Todd Harms user_jones So when James Taavon responds, he becomes the sender and Denise Johnson is the receiver. When Todd Harms responds, he becomes the sender and Joe Smith is the receiver. Is this correct? I'm trying to get an understanding of how your db structure works so I can assist. - Matt Small -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:35 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Mark, Perhaps I am confusing you. On my output i am using the same query at least that is what i was planning to do. There is also a query just for the role. I am not sure if I can delete it and use the other instead. cfquery name=GetRole datasource=lsar_beta select role fromtbl_users where username = '#session.username#' /cfquery -- OUTPUT FOR KEY USER cfif getrole.role IS user_self Sandy Crisafulli cfelseif getrole.role CONTAINS trimble cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role IS user_miller cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS reich_godd cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS cooper cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS feinroth cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS edmounds cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelse /cfif -- OUTPUT FOR USER cfoutputinput type=Text name=sender value=#getkeyuser.fullname# size=20 readonly/cfoutput -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:21 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? nope, only one person is designated as key role, but there are about six different ones, one for each manager. -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:22 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Ummm.. I don't quite understand how that code is returning two instances of the key_role unless you have it in the database twice. Am I missing something here? If it does produce two instances, are they both the same? If they are, use the distinct keyword: cfquery name=GetKeyUser datasource=lsar_beta select distinct fullname, role fromtbl_users where fullname IN (select fullname from tbl_users where username = '#session.username#') /cfquery -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:07 AM To: CF-Talk Subject: RE: Subquery from the same table
RE: Subquery from the same table: Is it possible??
ok, Matt. Thanks. I will get back to you on this. Thanks for your assistance. -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 3:32 PM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? If I have this correct, then the below query should work. I inserted the data that I made up into a table and I executed the following query on it - no problems. You may need to inspect your table for duplicates if you are getting more than one return. Or, it is possible for more than one instance of key_user_miller in the role field of your table? - Matt Small cfquery name=GetKeyUser datasource=lsar_beta select fullname, role from tbl_users whererole = ( select 'key_' role as keyrole from tbl_users where username = '#session.username#') /cfquery -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 2:59 PM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? CORRECT!! -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 2:24 PM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? I am very confused now as to what you are trying to accomplish. Maybe you could show a little more of your database. As I understand it, you have about six managers each with a key user. You have a number of employees whom each fall under a manager. When an employee responds to a form, the employee's fullname goes into sender and the manager's name goes into the receiver. Table USERNAMEFULLNAMEROLE djohnsonDenise Johnson key_user_miller twendel Tom Wendel user_miller msmall Matt Small user_miller jtaavon James Taavonuser_miller jsmith Joe Smith key_user_jones wwilliams Wendy Williams user_jones tharms Todd Harms user_jones So when James Taavon responds, he becomes the sender and Denise Johnson is the receiver. When Todd Harms responds, he becomes the sender and Joe Smith is the receiver. Is this correct? I'm trying to get an understanding of how your db structure works so I can assist. - Matt Small -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:35 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Mark, Perhaps I am confusing you. On my output i am using the same query at least that is what i was planning to do. There is also a query just for the role. I am not sure if I can delete it and use the other instead. cfquery name=GetRole datasource=lsar_beta select role fromtbl_users where username = '#session.username#' /cfquery -- OUTPUT FOR KEY USER cfif getrole.role IS user_self Sandy Crisafulli cfelseif getrole.role CONTAINS trimble cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role IS user_miller cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS reich_godd cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS cooper cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS feinroth cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelseif getrole.role CONTAINS edmounds cfoutputinput type=text name=receiver value=#getkeyuser.fullname# size=20 readonly/cfoutput cfelse /cfif -- OUTPUT FOR USER cfoutputinput type=Text name=sender value=#getkeyuser.fullname# size=20 readonly/cfoutput -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:21 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? nope, only one person is designated as key role, but there are about six different ones, one for each manager. -Original Message- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 11:22 AM To: CF-Talk Subject: RE: Subquery from the same table: Is it possible?? Ummm.. I don't quite understand how that code is returning two instances of the key_role unless you have it in the database twice. Am I missing something here? If it does produce two instances, are they both the same? If they are, use the distinct keyword: cfquery name=GetKeyUser datasource=lsar_beta select distinct fullname, role fromtbl_users where fullname IN (select fullname from
