0

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.

3dSpatialUser
  • 2,034
  • 1
  • 9
  • 18
  • 2
    Tip of today: Always use modern, explicit `JOIN` syntax! Easier to write (**without errors**), easier to read and maintain, and easier to convert to outer join if needed. – jarlh Feb 10 '22 at 11:22
  • Consider using a `union`, your example above is producing a cartesian product. – Stu Feb 10 '22 at 11:23
  • 1
    It is usually bad schema design to have two 'identical' tables. You have found one of the reasons. – Rick James Feb 10 '22 at 18:00

1 Answers1

1

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.

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • Hi, thank you for your answer, but I get the follow error: `ERROR 1222 (21000): The used SELECT statements have a different number of columns`. This is correct, all tables contain the `ID` column, but they can have additional columns with extra information. Besides this error, with this method I have to know all tables beforehand, it is not possible to union all tables in a database right? – 3dSpatialUser Feb 10 '22 at 12:18
  • When you do not know all tabled beforehand, not much is possible at all. The only thing left is to replace `*` by `ID`, I will edit in a couple of minutes. – Luuk Feb 10 '22 at 12:45
  • Oke, I think there is no real solution in pure SQL based on your answer, but thank you for your time and explanation. I will accept your answer. – 3dSpatialUser Feb 10 '22 at 12:50