Select Data From Multiple Database In a Query

selecting data from multiple database/schema in a single mysql/mssql/oracle query

database :A table :employee

   employee
   (                                    
       `EMPLOYEE_ID` int(10) NOT NULL auto_increment,             
       `FIRST_NAME` varchar(100) default NULL,                      
       `DEPARTMENT_ID` int(10) NOT NULL,                       
       PRIMARY KEY  (`EMPLOYEE_ID`),
       FOREIGN KEY  (`DEPARTMENT_ID`) REFERENCE B.deparement(DEPARTMENT_ID)
   ) 
 

database :B table :deparement

  deparement
  (                                    
    `DEPARTMENT_ID` int(10) NOT NULL auto_increment,             
    `DEPARTMENT` varchar(200) default NULL,                      
    `LOCATION_ID` int(10) NOT NULL,                       
    PRIMARY KEY  (`DEPARTMENT_ID`),
    FOREIGN KEY  (`LOCATION_ID`) REFERENCE C.location(LOCATION_ID)
  ) 
 

database :C table :location

  location
  (                                    
    `LOCATION_ID` int(10) NOT NULL auto_increment,             
    `LOCATION` varchar(200) default NULL,                      
    PRIMARY KEY  (`LOCATION_ID`)
  ) 
 

Using PHP we can retrive data from multiple database/schema as bellow

   <?php
      /*
     |-------------------------------------------------
     | CASE :
     | we want to select FIRST_NAME, DEPARTMENT and LOCATION
     | of employee using a single query from multiple database
     | provided all databases are in same server.
     |-------------------------------------------------
     */
     
     $sql = "
      SELECT  E.FIRST_NAME, D.DEPARTMENT, L.LOCATION
      FROM   A.employee AS E
      LEFT JOIN B.deparement AS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
      LEFT JOIN C.location AS L ON L.LOCATION_ID = D.LOCATION_ID
     ";
     /*
     |-------------------------------------------------
     | that's it. if you want to add filter option
     | you can do this by concating where clause.
     |-------------------------------------------------
     */
     $sql .= " WHERE E.EMPLOYEE_ID = 100 ";
     /*
     |-------------------------------------------------
     | if you like to sort the order, you can do this :
     |-------------------------------------------------
     */
     $sql .= " ORDER BY E.EMPLOYEE_ID, D.DEPARTMENT_ID, C.LOCATION_ID ";
     /*
     |-------------------------------------------------------------------------------
     | USEING INDEXED COLUMN IN WHERE and ORDER BY CLAUSE INCREASE QUERY PERFORMANCE
     |-------------------------------------------------------------------------------
     */
    ?>
 

 

Related Tutorial Examples

1. MySQL Sample Database Script Like Oracle HR Schema
2. MySQL Sub Query: INSERT INTO SELECT... Example
3. MySQL Sub Query: INSERT INTO SELECT *... Example
4. MySQL Sub Query: CREATE TABLE SELECT... Example
5. MySQL Sub Query: CREATE TABLE SELECT *... Example
6. MySQL TRUNCATE TABLE Example
7. Parentheses In SQL Command MySQL Example
8. 19 Examples to Learn MySQL
9. Email Address Validation PHP Regular Expression Example
10. 28 Basic Tutorials to Learn Oracle