James,
You wrote
**********
where IN (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:
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 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
