Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Query multiple DBs
- Thread starter tina2
- Start date
- Thread starter
- #3
Thanks Kola.
I didn't find it on Ben Forta's site, but I found quite a bit in the Macromedia forums. I understand the concept, but I'm having trouble with the following query. I have a WebUser and WebUserLookup table in the main database, and I am trying to pull the corresponding policy information from the second DB (ZenithWeb2). CF is not able to evaluate PolicyID from my Q of Qs. I'm thinking I need to do an inner join in the third query, but I'm not sure how...
<CFQUERY NAME="qGetPolicy" DATASOURCE="ZenithWeb">
SELECT WebUsers.UserID, WebUserLookup.UserID, WebUserLookup.PolicyID, Policy.PolicyID, Policy.name
FROM (WebUsers INNER JOIN WebUserLookup ON WebUsers.UserID = WebUserLookup.UserID) INNER JOIN Policy ON [WebUserLookup.PolicyID = Policy.PolicyID] AS PolicyID
WHERE WebUsers.UserID = #session.UserID#
ORDER BY Policy.name
</CFQUERY>
<CFQUERY NAME="qGetPolicy2" DATASOURCE="ZenithWeb2">
SELECT Policy.PolicyID, Policy.name
FROM Policy
</CFQUERY>
<cfquery NAME="qOfQgetPolicy" dbtype="query">
SELECT qGetPolicy.PolicyID, qGetPolicy.UserID, qGetPolicy.Name, qGetPolicy2.PolicyID, qGetPolicy2.Name
FROM qGetPolicy, qGetPolicy2
WHERE qGetPolicy.PolicyID = qGetPolicy2.PolicyID AS PolicyID
</cfquery>
Does anyone have any ideas?
Kristine
I didn't find it on Ben Forta's site, but I found quite a bit in the Macromedia forums. I understand the concept, but I'm having trouble with the following query. I have a WebUser and WebUserLookup table in the main database, and I am trying to pull the corresponding policy information from the second DB (ZenithWeb2). CF is not able to evaluate PolicyID from my Q of Qs. I'm thinking I need to do an inner join in the third query, but I'm not sure how...
<CFQUERY NAME="qGetPolicy" DATASOURCE="ZenithWeb">
SELECT WebUsers.UserID, WebUserLookup.UserID, WebUserLookup.PolicyID, Policy.PolicyID, Policy.name
FROM (WebUsers INNER JOIN WebUserLookup ON WebUsers.UserID = WebUserLookup.UserID) INNER JOIN Policy ON [WebUserLookup.PolicyID = Policy.PolicyID] AS PolicyID
WHERE WebUsers.UserID = #session.UserID#
ORDER BY Policy.name
</CFQUERY>
<CFQUERY NAME="qGetPolicy2" DATASOURCE="ZenithWeb2">
SELECT Policy.PolicyID, Policy.name
FROM Policy
</CFQUERY>
<cfquery NAME="qOfQgetPolicy" dbtype="query">
SELECT qGetPolicy.PolicyID, qGetPolicy.UserID, qGetPolicy.Name, qGetPolicy2.PolicyID, qGetPolicy2.Name
FROM qGetPolicy, qGetPolicy2
WHERE qGetPolicy.PolicyID = qGetPolicy2.PolicyID AS PolicyID
</cfquery>
Does anyone have any ideas?
Kristine
the problem lies i think in thr where statement of your final query(Q of Q's)
WHERE qGetPolicy.PolicyID = qGetPolicy2.PolicyID AS PolicyID
you need to assign an alias to a field before this point in the select statement.
so your q of q's will read:
<cfquery NAME="qOfQgetPolicy" dbtype="query">
SELECT qGetPolicy.PolicyID, qGetPolicy.UserID, qGetPolicy.Name, qGetPolicy2.PolicyID AS QPOLICYID, qGetPolicy2.Name
FROM qGetPolicy, qGetPolicy2
WHERE qGetPolicy.PolicyID = qGetPolicy2.QPOLICYID
</cfquery>
good luck
tony
WHERE qGetPolicy.PolicyID = qGetPolicy2.PolicyID AS PolicyID
you need to assign an alias to a field before this point in the select statement.
so your q of q's will read:
<cfquery NAME="qOfQgetPolicy" dbtype="query">
SELECT qGetPolicy.PolicyID, qGetPolicy.UserID, qGetPolicy.Name, qGetPolicy2.PolicyID AS QPOLICYID, qGetPolicy2.Name
FROM qGetPolicy, qGetPolicy2
WHERE qGetPolicy.PolicyID = qGetPolicy2.QPOLICYID
</cfquery>
good luck
tony
- Thread starter
- #6
Thanks Ya'll!
pigsie, the Inner Join does work when I run the querey on its own. (I'm no SQL expert either, but I used the Access query builder ;-))
Tony, I used your suggestion, but I assigned the alias in the second query:
<CFQUERY NAME="qGetPolicy2" DATASOURCE="ZenithWeb2">
SELECT Policy.PolicyID AS QPOLICYID, Policy.name
FROM Policy
</CFQUERY>
and called it in the Q of Qs, which didn't throw an error, but I'm back to the problem of evaluating "qOfqGetPolicy.PolicyID"
The first place I call the query is to polulate a dropdown list with the policy names:
<option value="#valuelist(qOfqGetPolicy.PolicyID)#">All</option> Which works like a charm when I use the original query, but throws an error when I try to pull it from the Q of Qs...
I also got rid of the []AS PolicyID in the statement ON [WebUserLookup.PolicyID = Policy.PolicyID] AS PolicyID
in the first query, which was a lame attempt at passing the ID, which did not work.
Any ideas?
Kristine
pigsie, the Inner Join does work when I run the querey on its own. (I'm no SQL expert either, but I used the Access query builder ;-))
Tony, I used your suggestion, but I assigned the alias in the second query:
<CFQUERY NAME="qGetPolicy2" DATASOURCE="ZenithWeb2">
SELECT Policy.PolicyID AS QPOLICYID, Policy.name
FROM Policy
</CFQUERY>
and called it in the Q of Qs, which didn't throw an error, but I'm back to the problem of evaluating "qOfqGetPolicy.PolicyID"
The first place I call the query is to polulate a dropdown list with the policy names:
<option value="#valuelist(qOfqGetPolicy.PolicyID)#">All</option> Which works like a charm when I use the original query, but throws an error when I try to pull it from the Q of Qs...
I also got rid of the []AS PolicyID in the statement ON [WebUserLookup.PolicyID = Policy.PolicyID] AS PolicyID
in the first query, which was a lame attempt at passing the ID, which did not work.
Any ideas?
Kristine
is this what you q of q's now looks like ?
<cfquery NAME="qOfQgetPolicy" dbtype="query">
SELECT qGetPolicy.PolicyID, qGetPolicy.UserID, qGetPolicy.Name, qGetPolicy2.PolicyID, qGetPolicy2.Name
FROM qGetPolicy, qGetPolicy2
WHERE qGetPolicy.PolicyID = qGetPolicy2.QPolicyID
</cfquery>
if so then you have got two fields that are named the same in your select statement (policyID). change the qGetPolicy2.PolicyID to qGetPolicy.QPolicyID
CF doesn't know which one you are referring to in your output statement so just outputs the first one that it comes to !
if this doesn't help can you post up the three queries again
<cfquery NAME="qOfQgetPolicy" dbtype="query">
SELECT qGetPolicy.PolicyID, qGetPolicy.UserID, qGetPolicy.Name, qGetPolicy2.PolicyID, qGetPolicy2.Name
FROM qGetPolicy, qGetPolicy2
WHERE qGetPolicy.PolicyID = qGetPolicy2.QPolicyID
</cfquery>
if so then you have got two fields that are named the same in your select statement (policyID). change the qGetPolicy2.PolicyID to qGetPolicy.QPolicyID
CF doesn't know which one you are referring to in your output statement so just outputs the first one that it comes to !
if this doesn't help can you post up the three queries again
- Thread starter
- #8
There lies the problem!
ZenithWeb and ZenithWeb2 have identical feilds. I need to get the PolicyID and Policy.Name from both DBs and display them in one page.
<---! first query pulls the userID from the users table,and the userlookup table, which contains corresponding policyIDs from both DBs --->
<CFQUERY NAME="qGetPolicy" DATASOURCE="ZenithWeb">
SELECT WebUsers.UserID, WebUserLookup.UserID, WebUserLookup.PolicyID, Policy.PolicyID, Policy.name
FROM (WebUsers INNER JOIN WebUserLookup ON WebUsers.UserID = WebUserLookup.UserID) INNER JOIN Policy ON WebUserLookup.PolicyID = Policy.PolicyID
WHERE WebUsers.UserID = #session.UserID#
ORDER BY Policy.name
<---! Second query pulls PolicyID and Policy Name from ZenithWeb2 --->
</CFQUERY>
<CFQUERY NAME="qGetPolicy2" DATASOURCE="ZenithWeb2">
SELECT Policy.PolicyID AS QPOLICYID, Policy.name
FROM Policy
</CFQUERY>
<---! Qof Qs needs to get the Policy.Name and PolicyID from both DBs --->
<cfquery NAME="qOfQgetPolicy" dbtype="query">
SELECT qGetPolicy.PolicyID, qGetPolicy.UserID, qGetPolicy.Name, qGetPolicy2.QPOLICYID, qGetPolicy2.Name
FROM qGetPolicy, qGetPolicy2
WHERE qGetPolicy.PolicyID = qGetPolicy2.QPOLICYID
</cfquery>
If I could combine the DBs, I would, but it is not feasible at this point.
Am I going about this all wrong?
Kristine
ZenithWeb and ZenithWeb2 have identical feilds. I need to get the PolicyID and Policy.Name from both DBs and display them in one page.
<---! first query pulls the userID from the users table,and the userlookup table, which contains corresponding policyIDs from both DBs --->
<CFQUERY NAME="qGetPolicy" DATASOURCE="ZenithWeb">
SELECT WebUsers.UserID, WebUserLookup.UserID, WebUserLookup.PolicyID, Policy.PolicyID, Policy.name
FROM (WebUsers INNER JOIN WebUserLookup ON WebUsers.UserID = WebUserLookup.UserID) INNER JOIN Policy ON WebUserLookup.PolicyID = Policy.PolicyID
WHERE WebUsers.UserID = #session.UserID#
ORDER BY Policy.name
<---! Second query pulls PolicyID and Policy Name from ZenithWeb2 --->
</CFQUERY>
<CFQUERY NAME="qGetPolicy2" DATASOURCE="ZenithWeb2">
SELECT Policy.PolicyID AS QPOLICYID, Policy.name
FROM Policy
</CFQUERY>
<---! Qof Qs needs to get the Policy.Name and PolicyID from both DBs --->
<cfquery NAME="qOfQgetPolicy" dbtype="query">
SELECT qGetPolicy.PolicyID, qGetPolicy.UserID, qGetPolicy.Name, qGetPolicy2.QPOLICYID, qGetPolicy2.Name
FROM qGetPolicy, qGetPolicy2
WHERE qGetPolicy.PolicyID = qGetPolicy2.QPOLICYID
</cfquery>
If I could combine the DBs, I would, but it is not feasible at this point.
Am I going about this all wrong?
Kristine
- Thread starter
- #10
I am beginning to think what I want to do is impossible...
I have made a few modifications, but I wont bore you with the query again. I don't think there is anything structurally wrong with the query. The problem is in what I want to accomplish with it.
For example: In the following Q of Qs how would I reference the #Name# feild? #CombinedQuery.WHAT?#
<cfquery datasource="DataName1" name="QueryName1">
SELECT Table1.Name as Name1, Table1.Email as Email1
FROM Table1
ORDER BY Table1.Name ASC
</cfquery>
<cfquery datasource="DataName2" name="QueryName2">
SELECT Table2.Name as Name2, Table2.Email as Email2
FROM Table2
ORDER BY Table2.Name ASC
</cfquery>
<cfquery dbtype="query" name="CombinedQuery">
SELECT QueryName1.Name1, QueryName1.Email1, QueryName2.Name2,
QueryName2.Email2
FROM QueryName1,QueryName2
</cfquery>
Am I asking for the moon?![[sadeyes]](/data/assets/smilies/sadeyes.gif "[sadeyes] [sadeyes]")
Kristine
I have made a few modifications, but I wont bore you with the query again. I don't think there is anything structurally wrong with the query. The problem is in what I want to accomplish with it.
For example: In the following Q of Qs how would I reference the #Name# feild? #CombinedQuery.WHAT?#
<cfquery datasource="DataName1" name="QueryName1">
SELECT Table1.Name as Name1, Table1.Email as Email1
FROM Table1
ORDER BY Table1.Name ASC
</cfquery>
<cfquery datasource="DataName2" name="QueryName2">
SELECT Table2.Name as Name2, Table2.Email as Email2
FROM Table2
ORDER BY Table2.Name ASC
</cfquery>
<cfquery dbtype="query" name="CombinedQuery">
SELECT QueryName1.Name1, QueryName1.Email1, QueryName2.Name2,
QueryName2.Email2
FROM QueryName1,QueryName2
</cfquery>
Am I asking for the moon?
Kristine
- Status
Similar threads
