Combine multiple SELECT statements

user_785928 0
Viewed 63494

I've used Excel to generate numerous SELECT statements from a list of the schema names from a database with a large number of identical schemas:

select result from foo.table limit 1;
select result from bar.table limit 1;
select result from doo.table limit 1;

(foo, bar & doo are examples of my schemas, there are hundreds in reality).

Each SELECT will return only one result. I simply want one column result with as many rows as there are schemas. I can then copy this back into Excel against the schema names.

When I run the query above I get 1 row, with the others being discarded:

Query result with 1 row discarded.

Query result with 1 row discarded.

Total query runtime: 40 ms.
1 row retrieved.

I have tried using UNION ALL, but the limit 1 I am using to ensure one row only is returned from each schema table appears to prevent this from working.

How can I either prevent the other rows from being discarded, or write a query that will return the values I need (two columns - schema_name, result - one row for each schema) in a more efficient way?

union postgresql sql
3 Answers
create view my_data1 
AS
with data as
(
    select student_id,sum(marks) as total_marks 
    from   marks_marks 
    group by 1
) , 
data1 as
(
    select id, name 
    from   students_class
), 
data2 as
(
    select applicant_name,
           id,
           class_name 
    from   students_students
)
select data2.applicant_name , 
       data1.name as class_name ,  
       data.total_marks 
from   data2 
join   data1  on data1.id = data2.class_name 
join   data   on data.student_id = data2.id



select * from my_data1
user_91492230
Related