JOIN 5 Database in MYSQL

Question

I have 5 Database, Let say their name is A B C D E

All database have the same table / structure / field

I want to get result from 5 database using table SMSOutgoing and the field is uid

It look like this :

SELECT * OR JOIN 5 database A B C D E F
FROM `table` SMSOutgoing
WHERE uid = 1

Not all the database have uid=1, it need to display which database have the result

I run SMS Gateway, each phone / 1 number using 1 database, thats why there is so many different database.

I spent hours to solve it but always error, I think i follow the wrong guide (JOIN multiple table in 1 database)

I'm Lost, please Help and Thank You

Answer 1

Sounds like you want to list the databases out that contain uid = 1 in the SMSOutgoing table. If so, you should be able to use UNION:

SELECT DISTINCT 'DatabaseA' WhichDb
FROM DatabaseA.SMSOutgoing 
WHERE uid = 1
UNION
SELECT DISTINCT 'DatabaseB' WhichDb
FROM DatabaseB.SMSOutgoing 
WHERE uid = 1
UNION
...
UNION
SELECT DISTINCT 'DatabaseF' WhichDb
FROM DatabaseF.SMSOutgoing 
WHERE uid = 1

I used DISTINCT in case you could have multiple uid in the same table -- that may be unnecessary.

EDIT: From your comments, it sounds like you just want the results:

SELECT *
FROM DatabaseA.SMSOutgoing 
WHERE uid = 1
UNION
SELECT *
FROM DatabaseB.SMSOutgoing 
WHERE uid = 1
UNION
...
UNION
SELECT *
FROM DatabaseF.SMSOutgoing 
WHERE uid = 1

You may need to use UNION ALL if you might have duplicates...