CODEWITHSUNDEEP
mysql
Shahab Haidar
Shahab Haidar

Reputation: 641

How to Get Data from Multiple Database Dynamically?

I have multiple databases in MySQL from Diffrent Companies. Ex. 

1.Company1
2.Company2
3.Company3
4.Company4

In every database the table and column's structure are same but data is stored for different companies. Now if i have to get a count of EmployeeID's sales for diffrent companies then i need to write the queries like below.

    Select a.EmployeeID,Count(b.TransactionDate)
    From Company1.Employee as a
    Inner Join Company1.Sales as b
    On a.EmployeeID=b.EmployeeID
    Group By a.EmployeeID
Union
Select a.EmployeeID,Count(b.TransactionDate)
    From Company2.Employee as a
    Inner Join Company2.Sales as b
    On a.EmployeeID=b.EmployeeID
    Group By a.EmployeeID
Union
Select a.EmployeeID,Count(b.TransactionDate)
    From Company3.Employee as a
    Inner Join Company3.Sales as b
    On a.EmployeeID=b.EmployeeID
    Group By a.EmployeeID
Union
Select a.EmployeeID,Count(b.TransactionDate)
    From Company4.Employee as a
    Inner Join Company4.Sales as b
    On a.EmployeeID=b.EmployeeID
    Group By a.EmployeeID

Notice i am changing the database in "FROM" Clause and "INNER JOIN" with hard-coded value.

In future further database may be added and i don't want to change the code behind or i don't want add code with another "union". Is there anything which we can do to do it dynamically. i mean if we can store database name in a table and query should automatically pick those database information from the table.

Upvotes: 1

Views: 477

Answers (1)

nbk
nbk

Reputation: 49385

I'm always waiting for upvotes and acknowledgements ;)

With this databases (they are all the same of course, so i post only one of them.

use `company3`;
DROP TABLE  IF EXISTS Employee;
CREATE TABLE Employee
(`EmployeeID` int, `LastName` varchar(40), `Firstname` varchar(40), `Age` int)
;

INSERT INTO Employee
(`EmployeeID`, `LastName`, `Firstname`, `Age`)
VALUES
(1, 'Hansen', 'Han', 30),
(2, 'Svendson', 'Sven', 23),
(3, 'Pettersen', 'Peter', 20)
;
DROP TABLE  IF EXISTS Sales;
CREATE TABLE Sales
(`EmployeeID` int, `TransactionDate` datetime)
;

INSERT INTO Sales
(`EmployeeID`, `TransactionDate`)
VALUES
(1, '2015-12-20 10:01:00'),
(1, '2015-12-20 10:01:00'),
(2, '2015-12-20 10:01:00'),
(2, '2015-12-20 10:01:00'),
(2, '2015-12-20 10:01:00')
;

And this stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSakesConut`()
BEGIN
  DECLARE bDone INT;
  DECLARE DBname TEXT;
  DECLARE sqlstement LONGTEXT;
  DECLARE n INT;
  DECLARE curs CURSOR FOR  SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
      WHERE SCHEMA_NAME LIKE 'company%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1; 
  OPEN curs;

  SET bDone = 0;
  SET n =0;
  SET sqlstement  = '';
  SALESloop: LOOP
    FETCH curs INTO DBname; 
    IF bDone = 1 THEN
      LEAVE SALESloop;
    END IF;
    IF n>0  THEN
     SET sqlstement  = CONCAT(sqlstement,' UNION ');
    END IF;
    SET sqlstement  = CONCAT(sqlstement,'Select "',DBname,'",a.EmployeeID,');
    SET sqlstement  = CONCAT(sqlstement,'Count(b.TransactionDate) ');
    SET sqlstement  = CONCAT(sqlstement,'From ',DBname,'.Employee as a ');
    SET sqlstement  = CONCAT(sqlstement,'Inner Join ',DBname,'.Sales as b ');
    SET sqlstement  = CONCAT(sqlstement,'On a.EmployeeID=b.EmployeeID ');
    SET sqlstement  = CONCAT(sqlstement,'Group By a.EmployeeID ');
    SET n =n+1;   

  END LOOP SALESloop; 
  CLOSE curs;
  SET @sqlstement = sqlstement;
  PREPARE stmt FROM @sqlstement;
  EXECUTE stmt;
END

For the explanation: For the cursor curs i get all Database names that start with compan In the loop i get one Dataase name after another and i build with it your select statement with the correct database names. And of course ou have to add union to all Select without the first

you get folloowing Result

company1   EmployeeID   Count(b.TransactionDate)
company1   1            2
company1   2            3
company2   1            2
company2   2            3
company3   1            2
company3   2            3

Of course i had to adept the select statement because yours didn't work properly.

Upvotes: 2

Related Questions