Solved

How to query from two different databases/datasources ?

Posted on 2006-10-22
9
1,247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
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
 

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

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

622 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