SQL select unique ID from multiple tables-CodePudding

I have multiple SQL tables, for example two tables with employees from office1 and office2. Each employer has an unique ID, which I have. I do not know in office the employer works so I want to select from both tables.

note in reality I have up to 50 tables, still with a unique ID.

database name for example employers

use employers
SHOW tables;

 --------------------- 
| Tables_in_employers |
 --------------------- 
| office1             |
| office2             |
 --------------------- 

SELECT * FROM `office1`, `office2` WHERE ID=1

Column `ID` in where clause is ambiguous

I have seen a solution like this: (SELECT * FROM multiple tables. MySQL)

SELECT * FROM `office1`, `office2` WHERE office1.ID=1 GROUP BY

But that statement only search in the office1 table, while I want to search both tables and only return the row where the ID matches.

Now I tend to use a for loop en go over each table (in python), but before I implement that I wanted to be sure that this can not be done (faster) in pure SQL.

CodePudding user response：

Use union:

SELECT ID, office
FROM (select ID, 'office' as 'office1' FROM office1
      union all
      select ID, 'office' as 'office2' FROM office2) allOffices
WHERE ID=1;

Or create a view:

create view allOffices as 
  select ID, 'office' as 'office1' from office1 
  union all 
  select ID, 'office' as 'office2' from office2;

After this your can do:

SELECT ID, office
FROM allOffices WHERE ID=1

The output will contain the column ID, and the columnd office with the name that is specified in the CREATE VIEW.

NOTE: it is possible that these queries return more than 1 row, if the ID exists in both offices.