CODEWITHSUNDEEP
sqlpostgresqlunion
Andrew M
Andrew M

Reputation: 383

Combine multiple SELECT statements

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?

Upvotes: 21

Views: 79864

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

Wrap individual sub-statements in parenthesis to make the syntax unambiguous:

(SELECT result FROM tbl1 LIMIT 1)
UNION ALL
(SELECT result FROM tbl2 LIMIT 1)

The manual about UNION is very clear on the matter:

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE, or FOR SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

Upvotes: 38

Sachin Burnawal
Sachin Burnawal

Reputation: 143

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

Upvotes: 10

Glenn
Glenn

Reputation: 9150

Wrapping in a subquery will get around it, but it gets a bit ugly.

SELECT result FROM (select 'a'::text AS result from foo limit 1) a
UNION ALL
SELECT result FROM (select 'b'::text AS result from bar limit 1) b

UPDATE

See Erwin's response. It is better.

Upvotes: 8

Related Questions