Solved

How to query from two different databases/datasources ?

Posted on 2006-10-22
9
1,240 Views
Last Modified: 2013-12-24
I'm new at this. I'm trying to create a query that brings data in from two different databases/datasources. (My queries with just one datasource work just fine.)
This works in my test environment (local pc, IIS, Windows XP, Dreamweaver & Coldfusion):

<cfquery name="rs_ratings" datasource="connCWArecipes">
SELECT r8.*, r.recipename, m.username
FROM cwarecipes.tblratings as r8, cwarecipes.tblrecipes as r, cwamembers.tblmembers as m
WHERE r8.recipeID = r.recipeID
AND r8.memberID=m.memberID
ORDER BY recipeID, daterated desc
</cfquery>

But on my hosting system (Apache/Unix) it generates this error:
Error Executing Database Query.  
General error: SELECT command denied to user 'CWARecipes'@'10.10.1.231' for table 'tblmembers'  
 
The error occurred in /vservers/cooki7xp/htdocs/admin/showratings.cfm: line 1
 1 : <cfquery name="rs_ratings" datasource="connCWArecipes">
2 : SELECT r8.*, r.recipename, m.username
3 : FROM CWARecipes.tblratings as r8, CWARecipes.tblrecipes as r, CWAMembers.tblmembers as m
--------------------------------------------------------------------------------
SQL    SELECT r8.*, r.recipename, m.username FROM CWARecipes.tblratings as r8, CWARecipes.tblrecipes as r, CWAMembers.tblmembers as m WHERE r8.recipeID = r.recipeID AND r8.memberID=m.memberID ORDER BY recipeID, daterated desc  
DATASOURCE   connCWArecipes
 
0
Comment
Question by:alicia1234
9 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 17784021
"General error: SELECT command denied to user 'CWARecipes'@'10.10.1.231' for table 'tblmembers'"

this tells you that there is an issue with permissions on the db itself, not anything wrong with your query. Assuming it's a mysql db, check out GRANT http://dev.mysql.com/doc/refman/5.0/en/grant.html to set permissions or contact your host to get them to do it.
0
 

Author Comment

by:alicia1234
ID: 17785636
I have no trouble accessing the db cwarecipes by itself ... no access problems at all .. it's only when I try to access both databases ... weird ...
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 17788266
some issues after closer look at your query

don't use "as" for a table alias afaik that is not proper syntax in mysql, mssql or access.

SELECT r8.*, r.recipename, m.username
FROM cwarecipes.tblratings r8, cwarecipes.tblrecipes r, cwamembers.tblmembers m
WHERE r8.recipeID = r.recipeID
AND r8.memberID=m.memberID
ORDER BY recipeID, daterated desc

It would also be better to use a join

SELECT r8.*, r.recipename, m.username
FROM cwarecipes.tblratings r8
inner join  cwarecipes.tblrecipes r on  r8.recipeID = r.recipeID,
inner join cwamembers.tblmembers m on r8.memberID=m.memberID
ORDER BY r.recipeID, r8.daterated desc


(i guessed on the tables for the order by clause adjust as needed)
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 17788331
you may also want to try adding the username and password attributes to your query which runs on the host...

Example:

<cfquery name="rs_ratings" datasource="connCWArecipes" username="YourDBUserName" password="YourDBPassword">
......
</cfquery>
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:alicia1234
ID: 17788643
I've been using "as" all along and have never had a problem with it.
0
 

Author Comment

by:alicia1234
ID: 17788739
.. and I double checked the MySQL documentation and "AS" is correct
0
 
LVL 36

Accepted Solution

by:
SidFishes earned 125 total points
ID: 17788861
i stand corrected ...

again, after closer inspection (hey it's really early here) I think the issue is that you'll need to do 2 selects and then a query of query in order to access the 2 datasources

since I can't tell which tables are in which datasource I'll just give you the outline

<cfquery name="Q1" datasource="DS1">
SELECT * from tbOne, tblTwo
</cfquery

<cfquery name="Q2" datasource="DS2">
SELECT * from  tblThree
</cfquery

<cfquery name="rs_ratings" dbtype="query">
SELECT * from q1 inner join q2 on q1.keyid = q2.keyid
</cfquery

0
 
LVL 2

Assisted Solution

by:nmarano
nmarano earned 125 total points
ID: 17791902
<cfquery name ="getratings_recipes" datasource="connCWArecipes">
Select *
From tblratings, tblrecipes
</cfquery>

<cfquery name ="getmembers" datasource="connCWArecipes">
Select *
From tblmembers
</cfquery>

<cfquery name="rs_ratings" dbtype="query">
SELECT *
FROM #getratings_recipes# innerjoin #getmembers#
WHERE #getratings_recipes.recipeID# = #getmembers.recipeID#
AND #getratings_recipes.memberID#=#getmembers.memberID#
ORDER BY recipeID, daterated desc
</cfquery>
0
 

Author Comment

by:alicia1234
ID: 17792046
Following is what ultimately worked. I had no luck with the inner joins that were recommended.
And the ## in nmarano's answer were incorrect. But SidFishes and nmarano's answers did get
me on the right track. Thanks.

<cfquery name ="getratings_recipes" datasource="connCWArecipes">
Select r8.*, r.recipename
From tblratings as r8, tblrecipes as r
where r8.recipeID = r.recipeID
</cfquery>

<cfquery name ="getmembers" datasource="connCWAmembers">
Select username, memberID
From tblmembers
</cfquery>

<cfquery name="rs_ratings" dbtype="query">
SELECT *
FROM getratings_recipes, getmembers
WHERE getratings_recipes.memberID = getmembers.memberID
ORDER BY recipeID, daterated desc
</cfquery>
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now