The trick is to join the table on itself.
<CFQUERY name="qEmployeeList" DATASOURCE="myDSN">
SELECT
emp.employee_number, emp.emp_last_name, emp.emp_preferred_name,
emp.email_id, sup.email_id AS SupervisorEmailID
FROM
tbl_Employee emp,
tbl_Employee sup
WHERE
emp.employee_number = #FORM.EmployeeName#
AND
emp.sup_short_name = sup.emp_short_name
</CFQUERY>
> -----Original Message-----
> From: Bosky, Dave [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, November 21, 2002 2:39 PM
> To: CF-Talk
> Subject: cfquery - subquerie>
>
>
> How would I go about combining these 2 queries into a single query?
> Or would it be best to leave it seperated into 2.
>
> Thx, Dave
>
> <CFQUERY name="qEmployeeList" DATASOURCE="myDSN">
> SELECT employee_number,emp_last_name, emp_preferred_name,
> sup_short_name, email_id
> FROM tbl_Employee
> WHERE employee_number = #FORM.EmployeeName#
> </CFQUERY>
>
> <CFQUERY NAME="qSupervisorData" DATASOURCE="myDSN">
> SELECT email_id
> FROM tbl_Employee
> WHERE emp_short_name = '#Trim(qEmployeeList.sup_short_name)#'
> </CFQUERY>
>
>
> HTC Disclaimer: The information contained in this message
> may be privileged and confidential and protected from
> disclosure. If the reader of this message is not the intended
> recipient, or an employee or agent responsible for delivering
> this message to the intended recipient, you are hereby
> notified that any dissemination, distribution or copying of
> this communication is strictly prohibited. If you have
> received this communication in error, please notify us
> immediately by replying to the message and deleting it from
> your computer. Thank you.
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
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
