We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

How to query from two different databases/datasources ?

alicia1234
alicia1234 asked
on
Medium Priority
1,274 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
 
Comment
Watch Question

CERTIFIED EXPERT

Commented:
"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.

Author

Commented:
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 ...
CERTIFIED EXPERT

Commented:
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)
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>

Author

Commented:
I've been using "as" all along and have never had a problem with it.

Author

Commented:
.. and I double checked the MySQL documentation and "AS" is correct
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
<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>

Author

Commented:
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>
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.