1

I have two Tables

**DB1**
users
prices
. . .

and

**DB2**
users
prices
. . .

Both are for a different Website, What I want to do is the make both users table a single table, And any user signs-up in one of the two website is signed in the other one too,

What I thought about was making a DB3 has users table, Then linking it with DB1 and DB2, But I really have no idea how to do that nor if this is the only way to make a single users table for two different websites at the same time.

Also I'll be using for example if there is Three Tables Queries needs to be connected with the 3rd Table like this

SELECT * FROM users[db3] INNER JOIN prices[db2] WHERE price > 0

in case I'm preforming a query of the db2 website, or

SELECT * FROM users[db3] INNER JOIN prices[db1] WHERE price > 0

in case it is on db1 website, How exactly do I do that?

Toleo
  • 764
  • 1
  • 5
  • 19
  • Are both databases running on the same MySQL server, separate MySQL servers on the same network, separate networks in the same datacenter, or separate datacenters? – The Impaler May 25 '18 at 14:32
  • @TheImpaler both databases are running on same server, Also I would like to know if they're both on different Server, How am i supposed to interact with it? With an API? – Toleo May 25 '18 at 14:33
  • 1
    The syntax is `db1.users` – Barmar May 25 '18 at 14:36
  • @NevilleKuyt This isn't a join, it should be a union. – Barmar May 25 '18 at 14:41
  • 1
    Take a look at the [MERGE storage engine](https://dev.mysql.com/doc/refman/8.0/en/merge-storage-engine.html) – Barmar May 25 '18 at 14:42

1 Answers1

1

I would keep a single users table on one database, let's say, on DB1.

From database DB2, I would just use:

SELECT * FROM DB1.users INNER JOIN prices WHERE price > 0;

See the prefix DB1. there? That way you are accessing a table from a different database.

You need to make sure you grant permissions (SELECT, UPDATE, DELETE, INSERT) on DB1 to the user on DB2.

I would strongly be against keeping multiple copies of the same table. They will easily go out of sync in no time and you'll surely start having all kinds of problems.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • I see, I always thought having the `dot .` part is always meant for aliases, In case the two databases in two different servers, do I use an API? – Toleo May 25 '18 at 14:39
  • The dot is used for separating database, table, and column names. – Barmar May 25 '18 at 14:40
  • 1
    Yeah, some databases use a dot for more things. For example, you can use **select * from CATALOG.SCHEMA.TABLE**. And if you have a "federated" database you could even add an extra prefix there: SERVICE.CATALOG.SCHEMA.TABLE. – The Impaler May 25 '18 at 14:42