How to query from two different databases/datasources ?
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'
The error occurred in /vservers/cooki7xp/htdocs/
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
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'
The error occurred in /vservers/cooki7xp/htdocs/
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
ASKER
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 ...
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)
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
......
</cfquery>
Example:
<cfquery name="rs_ratings" datasource="connCWArecipes
......
</cfquery>
ASKER
I've been using "as" all along and have never had a problem with it.
ASKER
.. and I double checked the MySQL documentation and "AS" is correct
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.memberI
ORDER BY recipeID, daterated desc
</cfquery>
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.memberI
ORDER BY recipeID, daterated desc
</cfquery>
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.