Joining two tables from different Databases

[mbarandao]

Hello,

Is it possible to join two tables from two different databases? I have the following query whereas the table named "calendar" is in a separate database than the table named "history". Any ideas on how to join bottom?

SELECT C.theDate as day, IFNULL( sum(case when DATE( CAST( H.timeentry AS DATE ) ) is null then 0 else 1 end) +   sum(numOfGuest), 0 ) AS total
FROM calendar AS C LEFT JOIN history AS H
ON C.theDate = DATE( CAST( H.timeentry AS DATE ) )
WHERE C.theDate BETWEEN STR_TO_DATE(('$from'), \"%Y-%m-%d\") AND STR_TO_DATE(('$to'), \"%Y-%m-%d\")
GROUP BY C.theDate ORDER BY C.theDate ASC

I appreciate any thoughts on this.

Mossa

[AleMonteiro]

If both databases are at the same server(MySQL Instance) you can just prefix the tables with the database name.

FROM database_1.calendar AS C LEFT JOIN database_2.history AS H

Make sure that the user logged in has permission to access both databases.

[mbarandao]

AleMonteiro,

Thank you! I will give it a try...

Mossa

[erikko]

The solution by AleMonteiro is the one that I am thinking of too.

[mbarandao]

thank you!