Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to query from two different databases/datasources ?

Posted on 2006-10-22
9
Medium Priority
?
1,250 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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 375 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 375 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

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

722 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