0

I need a joined (Union like) result from multiple databases on the same databaseserver in one query. Every customer database contains a location table and all customers are listed in the core database.

I don't need a simple join between to different databases. I need the actual joined database name to come from the same query.

I figure something like this.

SELECT customerlist.dbname,customerlist.realname,location.address
FROM core.customerlist
INNER JOIN `customer.dbname`.location
ORDER BY customerlist.realname

I know this won't work, I'm just trying to pseudo code what I'm searching for. I hope someone can help.

Database structure:

-- Database: `core`

CREATE TABLE IF NOT EXISTS `customerlist` (
  `realname` varchar(20) NOT NULL,
  `dbname` varchar(16) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `customerlist` (`realname`, `dbname`) VALUES
('Johnny', 'johnny'),
('Alfred', 'alfred');

-- --------------------------------------------------------
-- Database: `alfred`

CREATE TABLE IF NOT EXISTS `location` (
  `address` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `location` (`address`) VALUES
('House Three'),
('Car 1');

-- --------------------------------------------------------
-- Database: `johnny`

CREATE TABLE IF NOT EXISTS `location` (
  `address` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `location` (`address`) VALUES
('House One'),
('House Two');

Desired result;

johnny,Johnny,House One
johnny,Johnny,House Two
alfred,Alfred,House Three
alfred,Alfred,Car 1
Mureinik
  • 297,002
  • 52
  • 306
  • 350
Neergaard
  • 1
  • 1
  • 2
    possible duplicate of [MySQL -- join between tables in 2 different databases?](http://stackoverflow.com/questions/5698378/mysql-join-between-tables-in-2-different-databases) – bdunn Apr 27 '15 at 17:50
  • I have not done this before, and don't even know if you can do it, but you do really need to add more info, such as what exactly "doesn't work" means. Are you getting an error? Or just invalid data? Off the top of my head, you should be assigning synonyms to those table declarations, in case you have tables of the same name in multiple databases. – CargoMeister Apr 27 '15 at 17:53
  • What is the result of this? – Joop Apr 27 '15 at 17:55
  • If you add to your question the table definitions of the tables you are referring to, some sample data, and the desired results based on the sample data you give, someone should be able to help you. – Tom Apr 27 '15 at 18:05
  • This might just be in the question, but your backticks are wrong: `\`customer.dbname\`.location` should be `\`customer\`.\`dbname\`.location` – Dan Apr 27 '15 at 18:11
  • Change of backticks didn't help sorry. Plus I've added sample data and desired result – Neergaard Apr 27 '15 at 18:26

1 Answers1

0

You'll have to be careful of the two table identical tables...joining them in a union if fine since they are identical in structure:

 SELECT customerlist.dbname,customerlist.realname,location.address
 FROM core.customerlist
 LEFT JOIN (
   SELECT * FROM (johnny.location UNION alfred.location
   )) AS T2 ON customerlist.dbname = T2.dbname
 ORDER BY customerlist.realname
Pigasus
  • 130
  • 1
  • 2
  • 10
  • Doesn't solve the problem "johnny" and "alfred" are statically specified here. The query need to join the databases specified in core.customerlist dynamically. It could be two databases or >100. – Neergaard Apr 27 '15 at 20:12