In my new business job I encountered a database structure where tables are numbered like
table_1, table_2, table_3, ...
(up to 20)
All of that tables have the same structure like Id, Name, Surname. Now I got a request to show the Id's where the name is "John" in all tables. I struggle with the amount of tables I need to select and came up with this very long query:
With query_name (Id, Name, Surname) AS ( Select ID, Name, Surname FROM table_1 UNION ALL Select ID, Name, Surname FROM table_2 UNION ALL Select ID, Name, Surname FROM table_3 UNION ALL --... --all the other tables ) SELECT * FROM query_name WHERE Name = "John"
Is there a way to shrink my query or is there another solution for this problem? I thought of a loop but I were not able to create one within a "with as"-statement.