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
where role = ( 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
USERNAME FULLNAME ROLE
djohnson Denise Johnson key_user_miller
twendel Tom Wendel user_miller
msmall Matt Small user_miller
jtaavon James Taavon user_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
from tbl_users
where username = '#session.username#'
</cfquery>
-------------- OUTPUT FOR KEY USER ----------------
<cfif getrole.role IS "user_self">
Sandy Crisafulli
<cfelseif getrole.role CONTAINS "trimble">
<cfoutput><input type="text" name="receiver"
value="#getkeyuser.fullname#"
size="20" readonly></cfoutput>
<cfelseif getrole.role IS "user_miller">
<cfoutput><input type="text" name="receiver"
value="#getkeyuser.fullname#"
size="20" readonly></cfoutput>
<cfelseif getrole.role CONTAINS "reich_godd">
<cfoutput><input type="text" name="receiver"
value="#getkeyuser.fullname#"
size="20" readonly></cfoutput>
<cfelseif getrole.role CONTAINS "cooper">
<cfoutput><input type="text" name="receiver"
value="#getkeyuser.fullname#"
size="20" readonly></cfoutput>
<cfelseif getrole.role CONTAINS "feinroth">
<cfoutput><input type="text" name="receiver"
value="#getkeyuser.fullname#"
size="20" readonly></cfoutput>
<cfelseif getrole.role CONTAINS "edmounds">
<cfoutput><input type="text" name="receiver"
value="#getkeyuser.fullname#"
size="20" readonly></cfoutput>
<cfelse>
</cfif>
-------------- OUTPUT FOR USER
----------------
<cfoutput><input 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
from tbl_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
from tbl_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
where role = ( 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:
USERNAME FULLNAME ROLE
djohnson Denise 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
where IN (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
---------------------------------------- --------------------
White Johnson
Green Marjorie
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
______________________________________________________________________
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
