Querying two datasources, but how?

[Captain Ru]

Datasource HelpDesk - Tables: HelpDeskList A, Volunteers B,
Datasource Employee - Tables: Employee C.

A.Emailto, A.Entry_ID, A.Name
B.Name, B.Email
C.Notify, C.Email

closeticket = a.entry_id get a.emailto
a.name = b.name get b.mail
a.emailto = c.email where notify = yes

when ticket closes, get the 'email to' and send email if the person's employee
record has notify set to yes.include email of ticket closer, ticket id (entry
id)

help help please

<!--- <cfquery name = "onNotify" datasource = "HelpDesk">
select a.notify, b.emailto, b.name, c.Vemail
from emptbl a, Help_Desk_List b, TechsTbl c
where b.EntryID = #Close_ticket# and b.name = c.name and a.notify = 'Yes'
</cfquery> --->

<!--- <cfoutput query = "onNotify">
<cfif notify is "Yes">
<cfmail to="#Emailto#@firstbankak.com" from="Helpdesk@FirstbankAK.COM"
subject="Help Desk issue ###close_ticket# resolved by #name#"
server="10.1.100.202">
To better help us serve our customers you have volunteered to complete a
survey rating our quality of service.
Please take a moment to fill out the survey at the link below.


[url]http://fbak.firstbankak.com/helpdesk_test/new/emp/survey.cfm?ticket=#close_ticke[/url]
t#

We appreciate your time, and value your input.

Any questions you'd like to ask directly? <a href="mailto:#Vemail#">
</cfmail>
</cfif>
</cfoutput> --->

[The ScareCrow]

What database are you using ?

If it's an enterprise db, then you should be able to select from more than one
db using the single dsn

If it's 2 ms access db's then you will need to use query od queries.

Ken

[yalta]

At least for Oracle you can create aliases on the db level for your user specified in the DSN, and the aliases can even point to a separate instance of Oracle on a separate machine

[Captain Ru]

They are two access databases, but the thing is, I dont' know HOW to query two different datasources, syntax-wise.

i.e.
<cfquery datasource = "1" datasource2 = "2" name = "dblDS">
?

[Mountain Lover]

here's a quick one:

<CFQUERY NAME="q1" DATASOURCE="#yourDSN#">
SELECT P.yourfield, SU.UserFirstName & ' ' & SU.UserLastName AS UserFullName
FROM [A full path to an .mdb file].Users SU INNER JOIN Table1 P ON
SU.UserID = P.UserID
</CFQUERY>

HTH

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[The ScareCrow]

Interesting Tim, did not know you could do that.

I would have used QoQ

<cfquery name="qryHelpDesk" datasource="dsn">
Select EmailTo, Entry_ID, A.Name, Email
From HelpDeskList A Inner Join Volunteers B On A.EmailTo = B.Email
</cfquery>
<cfquery name="qryEmployee" datasource="dsn1">
Select Notify, Email
From Employee
</cfquery>
<cfquery name="qryCombine" dbtype="query">
Select *
From qryHelpDesk, qryEmployee
Where qryHelpDesk.Email = qryEmployee.Email
</cfquery>

The above is assuming that the common key field is the Email address

Ken