1

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.

Improve this question
4
  • You could have a table which stores all of the table names you need to pull from, but other than that there would be no way to do it in pure SQL, you'd have to write a script Commented Aug 16, 2019 at 15:08
  • I don't see that there's anything preventing you from having one database instead of n Commented Aug 16, 2019 at 15:24
  • i would also write a stored procedure, first to determin which dataases exists and the build an statement automatically Commented Aug 16, 2019 at 15:27
  • Hi @nbk ..!! waiting for your revert with the procedure query Commented Aug 16, 2019 at 16:03

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Hi @nbk Your procedure is absolutely correct. but i am struggling with the UNION Clause. If i am using n>0 then it is concating the union with every statement but putting the union upper on the query and giving an mysql query error. e.g UNION Select ...
The union only can be used between the select statement. To check your changes, add a # before PREPARE and EXECUTE Lines and add a SELECT @sqlstement; The result is the complete statement that you can test. You only should change the SET sqlstement = after the if Statement, There you make the SELECT statement
Correct but it is adding Union before the select statements.
Rather than adding it between the select statement
I have run the statement again in my server it works perfectly because for the first run of the loop, it doesn't add a union because n is itialiazed with 0 in the line SET n= 0; After the first run n is increased, so that the union will be added with alle further runs of the loop. The loop ends when it gets no more new row and sets bDone to 1. This triggers the if Clause. I simply don't get what you are doing. the only thing you must have is the initialisation of n before the loop and the increasing of n has to be after the n if clause. and i explained how you can check your changes.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.