CODEWITHSUNDEEP
sql
chrise
chrise

Reputation: 4253

SQL querying multiple schemas

I am looking to run a query on several schemas in workbench. bascially, they are all symmetric , just different dates. In workbench, i can only select one of them and run the query. Is there a way to aggregate them and run the query over a selection of schemas?

EDIT: To elaborate a bit more, I have schemas with names yyyy_mm_dd for each day. Ideally, instead of doing a union over them as suggested by Guish below, If would like a dynamic query that would be able to turn the name of the schema into a valid date and Union all of them where the date is within a defined range. Is this possible? I am using Oracle and sql workbench

Upvotes: 1

Views: 2164

Answers (1)

Guish
Guish

Reputation: 5160

I guess you are using mySql workbench.

Use an union operator.

(SELECT a FROM `schema1`.`t1` )
UNION
(SELECT a FROM `schema2`.`t1`);

Info here

You can then create a view from your query.

A thread here on querying multiple shema

In know Transact-SQL a lot more and it is similar.

SELECT ProductModelID, Name
FROM Schema1.ProductModel
UNION ALL
SELECT ProductModelID, Name
FROM Schema2.ProductModel
ORDER BY Name;

Upvotes: 1

Related Questions