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

Recommended Answers

All 4 Replies

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.

AleMonteiro,

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

Mossa

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

thank you!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.