select same table in multiple schema's without using union all

Question

Schema Name's: A,B

Both schema have same Table :

Table Name: stock. Feilds no,Stname

I want retrive details from stock table in two schams.

So i use this query :

 select no,stname from A.stock union all select no,stname from B.stock

I want to get the tables detail with out union all. Is it possible ? How to do that ?

I am using postgresql 9.0

@Houari Sir in my real if i am using its create a big query like my query size is 9 page in msword. if i connect the multiple schemas in single query is reduce the query in my project — Sathish
– Sathish, Commented Feb 28, 2014 at 9:46
What about creating a view that bring you those union all of all your stock tables, and in your project you have to just select query this view ? — Houari
– Houari, Commented Feb 28, 2014 at 9:50
i use select query only sir. And my some time my table have same no or same stname. And how to use view in this case sir @Houari — Sathish
– Sathish, Commented Feb 28, 2014 at 9:53

Houari · Accepted Answer · 2014-02-28 09:57:33Z

1

Here is how you view looks like:

CREATE OR REPLACE VIEW union_of_my_stock_tables AS 
 select no,stname from A.stock 
 union all 
 select no,stname from B.stock;
union all 
 select no,stname from C.stock;
union all 
 select no,stname from D.stock;

And in your project you can query this view by:

select * from union_of_my_stock_tables;

answered Feb 28, 2014 at 9:57

Houari

5,6513 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sathish Over a year ago

Thanks Sir. If i want my multiple tables combine in my query then i must create multiple view ah Sir?

Houari Over a year ago

How much cominaison do you have ?

Sathish Over a year ago

i have more than 5 tables in multiple schema's

Houari Over a year ago

No, i'm talking about your combinaisons!?

Sathish Over a year ago

Sorry sir i Can't understand?

Collectives™ on Stack Overflow

select same table in multiple schema's without using union all

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related