Last post Jan 29, 2013 08:24 AM by bbcompent1
Jan 22, 2013 09:08 AM|Megunticook|LINK
I need to create a view in a MySQL database that displays data from another MySQL database on another server. Ideally, I also want the view to include some data from the local MySQL database (using JOIN to include some data from one of the local tables).
Can I create a VIEW that queries multiple databases if one of those databases is on a remote server? I'm hung up on how to handle the connection string to the remote database, yet still preserving the credentials needed for the localhost.
Any ideas? Thanks.
Jan 22, 2013 09:57 AM|bbcompent1|LINK
Well first off you are going to have to create a Linked Server in your MySQL DB Server. Then when you run your queries, you would reference that linked server like this: (can't remember exactly off hand, google should find it quick enough)
Jan 22, 2013 09:59 AM|bbcompent1|LINK
Its called Federated Tables:
Jan 23, 2013 08:18 PM|Megunticook|LINK
Thanks. As it turns out, both databases are on the same server.
Can I simply prepend the name of the database? So to create the view it would look like this perhaps:
CREATE OR REPLACE VIEW my_view AS
database1.table1.field1 AS field1,
database2.table2.field2 AS field2;
Jan 29, 2013 08:24 AM|bbcompent1|LINK
Even though it is still on the same server it will still require federated tables: